Chapter 11: Advanced Updating Scenarios

Using SQL Server's NOCOUNT Setting

Many database administrators add logic to triggers and/or stored procedures to track the queries that applications run. A stored procedure that inserts a new row might look like this:

CREATE PROCEDURE spOrderInsert (@OrderID int OUTPUT, @CustomerID nchar(5),  @EmployeeID int, @OrderDate datetime) AS INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)     VALUES (@CustomerID, @EmployeeID, @OrderDate) SELECT @OrderID = SCOPE_IDENTITY() INSERT INTO OrdersLog (TypeOfChange, DateOfChange)     VALUES (@OrderID + ' added', GetDate()) RETURN

You can use this stored procedure as your DataAdapter object's InsertCommand to submit your changes successfully. Using a similar stored procedure to submit updates, however, will cause problems. Even worse, it might cause problems that you won't even discover until you've deployed your application.

To understand why, first look at the following update procedure that includes similar logging code:

CREATE PROCEDURE spOrderUpdate (@CustomerID_New nchar(5), @EmployeeID_New int,  @OrderDate_New datetime, @OrderID_Orig int,  @CustomerID_Orig nchar(5), @EmployeeID_Orig int,  @OrderDate_Orig datetime) AS UPDATE Orders     SET CustomerID = @CustomerID_New, EmployeeID = @EmployeeID_New,         OrderDate = @OrderDate_New     WHERE OrderID = @OrderID_Orig AND CustomerID = @CustomerID_Orig       AND EmployeeID = @EmployeeID_Orig AND OrderDate = @OrderDate_Orig IF @@ROWCOUNT = 1         INSERT INTO OrdersLog (TypeOfChange, DateOfChange)         VALUES ('Modified order ' + @OrderID_Orig, GetDate()) ELSE     INSERT INTO OrdersLog (TypeOfChange, DateOfChange)         VALUES ('Failed to modify order ' + @OrderID_Orig, GetDate()) RETURN

If you call this stored procedure to update an order from SQL Server's Query Analyzer, you'll see the following output in the Results window:

(1 row(s) affected) (1 row(s) affected)

Two rows affected? The stored procedure updated only one order. Including the primary key in the WHERE clause of the UPDATE query ensures that the query can modify at most one row. The second row that the stored procedure affected is the one that it inserted into the logging table.

You would see similar results if you built an ADO.NET Command object to call the stored procedure and update an order. The ExecuteNonQuery method would return 2.

This stored procedure adds an entry to the log table whether the update succeeds or fails. So, if the update attempt fails because the optimistic concurrency check failed, the stored procedure will still report that it affected one row.

You might understand that if the stored procedure affects one row it translates to a failed update attempt, but ADO.NET does not. The DataAdapter checks the number of rows that the query affected, and if it finds that the query affected zero rows, it interprets this result as a failed update attempt. Otherwise, it assumes that the update succeeded.

So, if you use this stored procedure as the UpdateCommand for your DataAdapter, the DataAdapter will always assume that the update attempt succeeded.

SQL Server lets you control whether queries report their results using the NOCOUNT setting. If we modify the update stored procedure by adding SET NOCOUNT ON before the UPDATE query and then call the stored procedure from Query Analyzer, we'll get the following result:

The command(s) completed successfully.

This doesn't necessarily mean that the update succeeded. It simply means that the query did not generate an error. The database does not consider a query that affects no rows a failure, even though you might.

Suppressing all the messages about the number of rows affected by the queries that the stored procedure executes will not solve the problem. If you use the stored procedure in its new state as a DataAdapter object's UpdateCommand, the DataAdapter will again assume that all updates succeed.

What we need to do is suppress the "row(s) affected" messages for all queries except the one that will attempt to update the desired row in the database. To do this, we can move the call to SET NOCOUNT ON so that it appears just after the UPDATE query in the stored procedure, as shown here:

CREATE PROCEDURE spOrderUpdate (@CustomerID_New nchar(5), @EmployeeID_New int,  @OrderDate_New datetime, @OrderID_Orig int,  @CustomerID_Orig nchar(5), @EmployeeID_Orig int,  @OrderDate_Orig datetime) AS UPDATE Orders     SET CustomerID = @CustomerID_New, EmployeeID = @EmployeeID_New,         OrderDate = @OrderDate_New     WHERE OrderID = @OrderID_Orig AND CustomerID = @CustomerID_Orig       AND EmployeeID = @EmployeeID_Orig AND OrderDate = @OrderDate_Orig SET NOCOUNT ON IF @@ROWCOUNT = 1         INSERT INTO OrdersLog (TypeOfChange, DateOfChange)         VALUES ('Modified order ' + @OrderID_Orig, GetDate()) ELSE     INSERT INTO OrdersLog (TypeOfChange, DateOfChange)         VALUES ('Failed to modify order ' + @OrderID_Orig, GetDate()) RETURN

If the stored procedure adds an entry to the log table before running our UPDATE query, we can use code that looks like this:

CREATE PROCEDURE MyUpdateProcedure (...) AS SET NOCOUNT ON INSERT INTO MyLogTable ... SET NOCOUNT OFF UPDATE MyTable SET ... RETURN

If you're unsure how the DataAdapter will interpret the results of the query you've specified in the InsertCommand, UpdateCommand, or DeleteCommand, call the ExecuteNonQuery method of the Command and check its return value. What is the return value when you supply parameters that cause the Command to succeed? What is the return value when you supply parameters that cause the Command to fail?