November 8, 2011 at 4:36 am
Hi I want to create a procedure like this
ALTER PROCEDURE usp_returncountofflag
AS
DECLARE @CountValue INT
set @CountValue= SELECT COUNT(ProductID)
FROM Production.Product
WHERE FinishedGoodsFlag=1
return @CountValue
what i exactly what is parameter should return count of ProductID with where condition.
But it returning error at SET statement.
Can some one help me how to get that value.
Thank you
November 8, 2011 at 5:15 am
SELECT @CountValue = COUNT(ProductID)
FROM Production.Product
WHERE FinishedGoodsFlag=1
However return is not used that way. Return for a procedure is for a status code to tell whether the procedure succeeded or failed. To pass data out, use an output parameter or return a resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 6:05 am
Just for reference, this SQL has the correction to the syntax error you received. Parantheses were missing around the select statement in the SET.
DECLARE @CountValue INT
set @CountValue= ( SELECT COUNT(ProductID)
FROM Production.Product
WHERE FinishedGoodsFlag=1 )
Despite that, use the method provided by Gail to set variables since it is easier to read and more straight forward.
November 8, 2011 at 6:11 am
Thank you
I am practicing Stored Procedures with my own queries.
Thank you both for help..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply