CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
m0fo
m0fo

Reputation: 2189

Call stored procedure fail due to Incorrect syntax

I have this current SP:

ALTER PROCEDURE [dbo].[GetHighestDrop]
  @val1 AS FLOAT ,
  @val2 AS FLOAT ,
  @val3 AS FLOAT 
AS
BEGIN
select case when @val1 < @val2 then
               case when @val1 < @val3 then @val1
               else @val3
               end
        when @val2 < @val3 then @val2
               else @val3
    end
END

I am calling it with this syntax:

SELECT
GetHighestDrop @val1=((clmnA/clmnB)-1)*100,@val2=2,@val3=3
FROM dbo.tstTable

clmnA and clmbB contain numbers, if I run them seperately, like:

SELECT ((clmnA/clmnB)-1)*100 FROM dbo.tstTable

I get a result, which is a float, but when I run it through GetHighestDrop, it fails.

Any idea why?

Upvotes: 0

Views: 144

Answers (2)

Andomar
Andomar

Reputation: 238296

You can only call a stored procedure with exec. There is no way to run a stored procedure inside a select.

Consider converting your procedure to a scalar user-defined function.

Example function use (don't forget the schema name, usually "dbo"):

select  dbo.GetHighestDrop( ((clmnA/clmnB)-1)*100, 2, 3) as Col1
from    dbo.tstTable

Upvotes: 3

christian.vogel
christian.vogel

Reputation: 2147

could it be that your stored procedure do not return any value? try out functions which has to return at least one value. if you want to use stored procedures, then you have to add an out parameter.

Upvotes: 0

Related Questions