SET NOCOUNT ON !!!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SET NOCOUNT ON !!!!!

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:16:12

Hi,
I have a doubt,where to use SET NOCOUNT ON on my Sproc like
i have a sproc where in which i have a select,insert and update operations have performed so do i have add SET NOCOUNT ON in every operations(Select/insert/update)of a single sproc?...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 08:19:27

Use it as the start of the procedure. Thats enough

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:22:34

Thank you..

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:25:04

I put this at the top of every Procedure and Trigger - immediately after the "AS":


SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

and as Madhi said just once is enough.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:40:20

oh!.OK.Sure.i will do add below 3 after AS of every sproc
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

i dont know about 2 and 3(SET XACT_ABORT ON,SET ARITHABORT ON)could please tell me about this.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:59:54

SET XACT_ABORT ON

Forces any current transaction to be rolled back - which has the effect of aborting any calling processes (that might not have caught the error that the child process had raised)

SET ARITHABORT ON

Treats divide-by-zero or arithmetic-overflow as a terminal error (roll back all transactions, so similar to above causes a cascade error). I have found that this solves some problems with unusual things - computed columns, views with INSTEAD OF Triggers, and so on. Generally I never use such "unusual things" any more as they are "trouble", but I still have this in all my Sprocs

Best to read the SQL Documentation about them - in particular SET XACT_ABORT ON may not suit everyone.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 09:19:04

Sure.i will Definetly read.Thanks Kristen..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources