Return Count

  • 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/

  • 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

  • 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

  • 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

  • only SELECT

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

  • 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/

  • 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/

  • 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

  • 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

  • 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/

  • 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

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

  • 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
  • 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.

  • 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]

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

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