Return Count

Question

Return Count

kapil_kk

SSC-Insane

Points: 21316
More actions
June 30, 2014 at 2:51 am

#308675

Hi all,
I have a scenario in which I need to capture count of rows returned by execution of stored procedure.
I new we can do it declaring OUTPUT parameter to a stored procedure but we are not allowed to modify the procedure.
So , is there any alternate way to achieve this scenario,
DECLARE @count int = 0
EXEC [dbo].[usp_test]
SET @count = (SELECT @@rowcount)
select @count
While doing this, it will always return 0 as rowcount.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259075 More actions · Answer 1

What does the stored procedure do? Insert, update, delete, select?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 2

kapil_kk (6/30/2014)
Hi all,
I have a scenario in which I need to capture count of rows returned by execution of stored procedure.
I new we can do it declaring OUTPUT parameter to a stored procedure but we are not allowed to modify the procedure.
So , is there any alternate way to achieve this scenario,
DECLARE @count int = 0
EXEC [dbo].[usp_test]
SET @count = (SELECT @@rowcount)
select @count
While doing this, it will always return 0 as rowcount.

I tried the same code and it worked. Please recheck whether the sp is resulting any rows or not

--Divya

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

kapil_kk (6/30/2014)
Hi all,
I have a scenario in which I need to capture count of rows returned by execution of stored procedure.
I new we can do it declaring OUTPUT parameter to a stored procedure but we are not allowed to modify the procedure.
So , is there any alternate way to achieve this scenario,
DECLARE @count int = 0
EXEC [dbo].[usp_test]
SET @count = (SELECT @@rowcount)
select @count
While doing this, it will always return 0 as rowcount.

Syntax is correct and it should retrieve the record count.

I am hoping your underlying procedure is not returning any rows. Please check the procedure.

Thanks

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

only SELECT

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Hardy21 (6/30/2014)
kapil_kk (6/30/2014)
Hi all,
I have a scenario in which I need to capture count of rows returned by execution of stored procedure.
I new we can do it declaring OUTPUT parameter to a stored procedure but we are not allowed to modify the procedure.
So , is there any alternate way to achieve this scenario,
DECLARE @count int = 0
EXEC [dbo].[usp_test]
SET @count = (SELECT @@rowcount)
select @count
While doing this, it will always return 0 as rowcount.
Syntax is correct and it should retrieve the record count.
I am hoping your underlying procedure is not returning any rows. Please check the procedure.

my stored procedure is returnign rows but @@rowcount is returning 0

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

Koen Verbeeck (6/30/2014)
What does the stored procedure do? Insert, update, delete, select?

Only Select

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259075 More actions · Answer 7

kapil_kk (6/30/2014)
only SELECT

It works fine here.

CREATE PROC dbo.MyTestProc AS

SELECT 1 AS Test;

GO

DECLARE @cnt INT;

EXEC dbo.MyTestProc;

SET @cnt = @@ROWCOUNT;

SELECT @cnt;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

kapil_kk (6/30/2014)
Hardy21 (6/30/2014)
kapil_kk (6/30/2014)
Hi all,
I have a scenario in which I need to capture count of rows returned by execution of stored procedure.
I new we can do it declaring OUTPUT parameter to a stored procedure but we are not allowed to modify the procedure.
So , is there any alternate way to achieve this scenario,
DECLARE @count int = 0
EXEC [dbo].[usp_test]
SET @count = (SELECT @@rowcount)
select @count
While doing this, it will always return 0 as rowcount.
Syntax is correct and it should retrieve the record count.
I am hoping your underlying procedure is not returning any rows. Please check the procedure.
my stored procedure is returnign rows but @@rowcount is returning 0

It is working fine in my case.

Thanks

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

Koen Verbeeck (6/30/2014)
kapil_kk (6/30/2014)
only SELECT
It works fine here.
CREATE PROC dbo.MyTestProc AS
SELECT 1 AS Test;
GO
DECLARE @cnt INT;
EXEC dbo.MyTestProc;
SET @cnt = @@ROWCOUNT;
SELECT @cnt;

Does it make any impact if I used SET NOCOUNT ON; in my stored procedure....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259075 More actions · Answer 10

kapil_kk (6/30/2014)
Koen Verbeeck (6/30/2014)
kapil_kk (6/30/2014)
only SELECT
It works fine here.
CREATE PROC dbo.MyTestProc AS
SELECT 1 AS Test;
GO
DECLARE @cnt INT;
EXEC dbo.MyTestProc;
SET @cnt = @@ROWCOUNT;
SELECT @cnt;
Does it make any impact if I used SET NOCOUNT ON; in my stored procedure....

No.

According to MSDN:

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT (Transact-SQL)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chris Wooding SSCarpal Tunnel Points: 4491 More actions · Answer 11

Is there any code after the Select, eg. tidy-up code?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

What does the procedure do? Can you post code?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kalpesh Kanjibhai Patel SSC Enthusiast Points: 182 More actions · Answer 13

Select 1 as [aliasname]

Result: 1 row(s) affected

Your result is [1 row(s) affected] so @@rowcount will have value 1.

SET NOCOUNT ON / OFF doesn;t matter with it.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 14

Check this setup:

CREATE PROC usp_Test

AS

DECLARE @t TABLE (v int)

SELECT 1 [Val]

GO

Now lets test:

EXEC usp_Test

SELECT @@ROWCOUNT

Stored proc retunrs resultset with one row and @@rowcount returns 1 as expected.

Not lets slightly change our proc:

ALTER PROC usp_Test

AS

DECLARE @t TABLE (v int)

SELECT 1 [Val]

DELETE @t

GO

Now test it again. Stored proc still returns exactly the same recordset of row, but @@rowcount is 0!

So, it is very important to see what exactly your stored proc is doing before exiting...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]