3

We have introduced a new data access framework for calling SQL Stored procedures. When calling a stored procedure that returns a recordset, we've run into problems where that stored procedure also performs an update (insert/update/delete) of some sort:

Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

The solution to this is to add 'SET NOCOUNT ON' to the top of the stored procedure. This works just fine, and, of course, it also has a touted performance enhancement.

We are recommending to developers that when they want to write code to call an existing stored procedure, they must also refactor the stored procedure itself to include SET NOCOUNT ON.

But, this got me into wondering, what would be the potential consequences/risks of performing a blanket update of all stored procedures to include SET NOCOUNT ON. Under what scenarios would this break an SPs functionality? (given that @@ROWCOUNT function is updated even when SET NOCOUNT is ON)

Help, as always, much appreciated.

James Wiseman
  • 29,946
  • 17
  • 95
  • 158
  • What framework is that error message from? Looks familiar. – Adam Houldsworth Mar 01 '11 at 14:26
  • The framework is custom, but uses ADO instead of ODBC. You get the error when you use ExecuteQuery on the described stored procedures. The accepted practice to to have an SP that doesn't U/I/D and return a recordset, but I'm delaing with legacy code here. – James Wiseman Mar 01 '11 at 14:32
  • Discussed here http://stackoverflow.com/questions/1483732/set-nocount-on-usage – Martin Smith Mar 01 '11 at 14:34

1 Answers1

5

I think the main danger would be if any of your existing processes look for and/or assume that the rowcount will be returned without explicitly querying the value of @@ROWCOUNT.

It's possible that somewhere in your code is a stored proc that gets executed, and the application waits for the return row value to know that it completed, in which case the app would hang indefinitely.

JNK
  • 63,321
  • 15
  • 122
  • 138