Post reply

SET NOCOUNT WITH EXEC of Dynamic SQL

  • August 3, 2010 at 10:05 am

    #227377

    Hi All,

    If I have a stored proc and I SET NOCOUNT ON and later on I also

    EXEC some dynamic SQL do I have to also pass SET NOCOUNT ON in the Dynamic SQL?

    Regards,

    OutOfTOuch

  • August 3, 2010 at 10:21 am

    #1201659

    No you don't need to.

    Test this:

    declare @sql nvarchar(1000)

    set @sql = 'select count(*) from sys.objects'

    exec sp_executesql @sql

    and then this:

    set nocount on

    declare @sql nvarchar(1000)

    set @sql = 'select count(*) from sys.objects'

    exec sp_executesql @sql

    Scope of "set nocount on" is a connection session (spid)

    _____________________________________________
    "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]

  • August 3, 2010 at 4:33 pm

    #1201840

    Thank You! This is Key: "Scope of "set nocount on" is a connection session (spid)"

    Do you know if SQLBOL says this anywhere?

  • August 4, 2010 at 3:45 am

    #1201945

    No, actually BOL doesn't tell anything about it in the "SET NOCOUNT" article. However you can find the list of SET statements of the session scope:

    http://msdn.microsoft.com/en-us/library/ms190356.aspx

    _____________________________________________
    "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]

  • August 4, 2010 at 9:35 am

    #1202155

    Thanks Again!

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

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