locked
ROWCOUNT RRS feed

  • Question

  • Is there any way to find value of ROWCOUNT (not @@ROWCOUNT) which we set by statement SET ROWCOUNT 1.
    Wednesday, August 6, 2008 8:05 AM

Answers

  •  

    Hi Muhammad,

     

    There is indeed:

     

    Code Snippet

    create table #DBCC

    (

    SetOption varchar(255),

    Value varchar(255)

    )

    declare @SetRowCount int

     

    INSERT INTO #DBCC

    EXEC ('DBCC useroptions WITH NO_INFOMSGS')

     

    select @SetRowCount = cast(Value as int) from #DBCC where SetOption = 'rowcount'

    select @SetRowCount as [Current SET ROWCOUNT value]

    drop table #DBCC

     

     

     

    As you're performing an insert from a resultset, the actual contents of the #dbcc table will depend upon the value of ROWCOUNT - so if it's 1 at the moment, the results of the above @SetRowCount variable will be null!

     

    Hope this helps anyway.

     

    Cheers,

    Rob

    Wednesday, August 6, 2008 2:43 PM

All replies

  • Hello,

     

    You can determine this via:

     

    Code Snippet
    DBCC USEROPTIONS

     

     

     

    Cheers,

    Rob

     

    Wednesday, August 6, 2008 8:14 AM
  • Thanks Robert!

    Is there any way to store this value into any variable?
    Wednesday, August 6, 2008 8:21 AM
  •  

    Hi Muhammad,

     

    There is indeed:

     

    Code Snippet

    create table #DBCC

    (

    SetOption varchar(255),

    Value varchar(255)

    )

    declare @SetRowCount int

     

    INSERT INTO #DBCC

    EXEC ('DBCC useroptions WITH NO_INFOMSGS')

     

    select @SetRowCount = cast(Value as int) from #DBCC where SetOption = 'rowcount'

    select @SetRowCount as [Current SET ROWCOUNT value]

    drop table #DBCC

     

     

     

    As you're performing an insert from a resultset, the actual contents of the #dbcc table will depend upon the value of ROWCOUNT - so if it's 1 at the moment, the results of the above @SetRowCount variable will be null!

     

    Hope this helps anyway.

     

    Cheers,

    Rob

    Wednesday, August 6, 2008 2:43 PM