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?

user4622594 · Accepted Answer · 2015-06-26 06:53:57Z

4

Append OUTPUT Keyword when executing the Procedure:

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

answered Jun 26, 2015 at 6:53

user4622594

Sign up to request clarification or add additional context in comments.

Comments

Vishwanath Dalvi · Accepted Answer · 2015-06-26 06:55:55Z

0

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

answered Jun 26, 2015 at 6:55

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related