June 30, 2014 at 2:51 am
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/
June 30, 2014 at 3:48 am
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
June 30, 2014 at 3:51 am
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
June 30, 2014 at 4:09 am
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
June 30, 2014 at 4:11 am
only SELECT
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 30, 2014 at 4:12 am
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/
June 30, 2014 at 4:12 am
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/
June 30, 2014 at 4:17 am
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
June 30, 2014 at 4:22 am
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
June 30, 2014 at 4:51 am
Koen Verbeeck (6/30/2014)
kapil_kk (6/30/2014)
only SELECTIt 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/
June 30, 2014 at 4:55 am
kapil_kk (6/30/2014)
Koen Verbeeck (6/30/2014)
kapil_kk (6/30/2014)
only SELECTIt 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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 1, 2014 at 4:05 am
Is there any code after the Select, eg. tidy-up code?
July 1, 2014 at 4:10 am
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
July 3, 2014 at 6:07 am
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.
July 3, 2014 at 8:41 am
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...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy