Reputation: 2189
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
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
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