Why is SQL rowcount not returning in my stored procedure output parameter?

Question

I have a stored procedure with pseudocode like this:

ALTER PROCEDURE myProcedure(@param1 int, @param2 int, @returnCode int output)
AS 
BEGIN
   SELECT .... -- my query here

   SET @returnCode = @@ROWCOUNT
END

However, when I execute this stored procedure, @returnCode is NULL:

DECLARE @returnCode INT
EXEC myProcedure 1, 1, @returnCode
SELECT @returnCode

Returns NULL.

However, if I just do a select within the proc rather than setting the return code - SELECT @@ROWCOUNT - I get the correct row count.

How can I return this row count in the output param?

score 4 · Accepted Answer · answered Jun 26 '15 at 06:53

4

Append OUTPUT Keyword when executing the Procedure:

DECLARE @returnCode INT
EXEC myProcedure 1, 1, @returnCode OUTPUT
SELECT @returnCode

answered Jun 26 '15 at 06:53

CeOnSql

2,615
1
16
38

score 0 · Answer 2 · answered Jun 26 '15 at 06:55

You missed output parameter.

CREATE PROCEDURE myProcedure(@param1 int, @param2 int, @returnCode int output)
AS 
BEGIN
   SELECT 1
   UNION 
   SELECT 2;
   SET @returnCode = @@ROWCOUNT
END

DECLARE @returnCode INT
EXEC myProcedure 1, 1, @returnCode OUTPUT
SELECT @returnCode

Why is SQL rowcount not returning in my stored procedure output parameter?

2 Answers2