Is the nocount in SQL for each connection or each execution?

Question

Is the nocount in SQL for each connection or each execution?

Consider the following

-- Proc to return data.  
-- nocount is set to on (no row count returned)
create procedure procOne as
  set nocount on
  select * from myTable

If I have an application that calls procOne and then performs a SQL call such as: DELETE from myTable where foo = bar

Will the delete statement return the number of rows deleted? I say yes.

The procedure should only set the nocount value within its execution. Please let me know if this is correct.

Thanks

Answer 1

It's for each execution, from the NOCOUNT docs:

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Answer 2

Yes. It resets the value of SET variable after the procedure has completed.

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

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