SQL Server Performance Forum – Threads Archive

SET NOCOUNT and transaction handling

Hi, We have a procedure like this: create proc proc1
@abc int
….
as
set nocount on
begin…
some business rule check here….
exec proc2 –exec proc 2
exec proc3 –exec proc 3
end In Procedure 3 one of the statement is failing with constraint violation ….if we set the set nocount on the code error out and rollback…but if do set nocount off it gives error but commit since we are not doing error handling in procedure….we are under impression that set nocount only related to network congestion…is nocount also involved in how sql servedo transaction handling? Thanks
–Harvinder

---

set nocount on only returns the number of rows affected. as far as I know it has nothing to do with how sql handle transactions.
You could add a check to proc3 to ensure there is no constraint violation before the operation. If you are inserting a value, for example, check before if value exists, return an int that will indicate error or success and based on that rollback or not your transaction. Bambola.

---

How is the procedure called? set nocount off will result in more info being sent back the client, depending on how the client handles this it could mask out an error having been raised…? Is there no transaction handling being done at all? If so, then the default behaviour is likely to be that every dml statement will commit as it is completed, so a subsequent statment rolling back would NOT roll back the previous successful ones Cheers
Twan

---

]]>