Encapsulating Update Operations in Stored Procedures

It is best to encapsulate your updates within stored procedures to improve both performance and security. Furthermore, you can implement several actions against different objects inside the database in the same procedure. Using stored procedures is an easy way to standardize procedures and calls from applications. The developer does not need to know how many tables to update because the process involves only a call this procedure action, thus passing the appropriate parameters.

To better understand how to implement stored procedure update operations, refer to the following considerations.

• Stored procedures execute near the data, which means directly inside the database. This translates into better performance.

• Stored procedures are compiled and contain the best query plan for their execution. This also translates into better performance.

• Performing several calls to the same or different stored procedures from an application can increase network traffic, which could result in a bottleneck.

• Implementing business logic inside stored procedures can be problematic . There are richer language elements in ADO.NET than in T-SQL. Moreover, a great deal of logic and calculation processing can be performed only by using cursors or temporary tables. Both require tremendous resources and decrease performance.

• More resource utilization ( mainly processor resources) implies more time expended, and more time implies more risk of losing the transaction and increasing timeout errors.

The following stored procedure updates a row in the HumanResources.Department table according to its primary key. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample03.sql.

 CREATE PROCEDURE [HumanResources].[DepartmentUpdate]    @DepartmentID int,    @Name nvarchar(50),    @GroupName nvarchar(50),    @ModifiedDate datetime AS BEGIN UPDATE [AdventureWorks].[HumanResources].[Department]    SET [Name] = @Name       ,[GroupName] = @GroupName       ,[ModifiedDate] = @ModifiedDate  WHERE DepartmentID=@DepartmentID END 

You can implement updates for changing the values for one or more columns in one or more tables. In the following example, the procedure accepts values for the subcategory and category names . If the procedure receives a value for the category name, it updates the column in the Category table. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample04.sql.

 CREATE PROCEDURE [Production].[SubcategoryUpdate]    @SubcategoryID int,    @Name nvarchar(50),    @CategoryName nvarchar(50)=null AS BEGIN    UPDATE [AdventureWorks].[Production].[ProductSubcategory]       SET        [Name] = @Name       ,[ModifiedDate] = GETDATE()       WHERE ProductSubcategoryID=@SubcategoryID    -- Check if we receive a Category Name    IF NOT @CategoryName IS NULL       -- Update the Category Name       UPDATE [AdventureWorks].[Production].[ProductCategory]          SET              [Name] = @CategoryName             ,[rowguid] = NEWID()             ,[ModifiedDate] = GETDATE()          WHERE          ProductCategoryID=           (              SELECT ProductCategoryID             FROM Production.ProductSubcategory             WHERE (ProductSubcategoryID = @SubCategoryID)           ) END 

In the following example, the ListPrice of the products will be changed by a percentage depending on the subcategory Id. The same procedure can be used to apply the percentage to all of the products if the second parameter is not passed during the procedures execution. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample05.sql.

 CREATE PROCEDURE [Production].[Product_ChangePrices]    @Percent decimal,    @ProductSubcategoryID int=null AS BEGIN    UPDATE [AdventureWorks].[Production].[Product]       SET [ListPrice] = [ListPrice] * @Percent     WHERE ([ProductSubcategoryID]=@ProductSubcategoryID)       -- If the second argument is null the percent applies to all the products       OR (@ProductSubcategoryID IS NULL) END 

Finally, the next example demonstrates how to use the same procedure to update or insert a row in a table, receiving values from the application in the parameters. This is a common scenario used in object-oriented programming to send the most updated information into the object once again. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample06.sql.

 CREATE PROCEDURE [Person].[AddressTypeFullUpdate]    @AddressTypeID int OUTPUT,    @Name nvarchar(50) OUTPUT,    @rowguid uniqueidentifier=null OUTPUT,    @ModifiedDate datetime=null OUTPUT AS -- Check if a row exists with the same AddressTypeID IF exists(          SELECT AddressTypeID          FROM Person.AddressType          WHERE (AddressTypeID = @AddressTypeID)       )    BEGIN -- Update the existing row       UPDATE [AdventureWorks].[Person].[AddressType]          SET [Name] = @Name            ,[rowguid] = NEWID()            ,[ModifiedDate] = GETDATE()        WHERE AddressTypeID = @AddressTypeID    END ELSE    BEGIN -- Insert a new row       INSERT INTO [AdventureWorks].[Person].[AddressType]                ([Name]                ,[rowguid]                ,[ModifiedDate])           VALUES                (@Name                ,NEWID(),                ,@GETDATE())    -- Retrieve the new AddressTypeID    SET @AddressTypeID=IDENT_CURRENT('Person.AddressType')    END -- Assign the actual values from the row to the parameters SELECT   @AddressTypeID=AddressTypeID,       @Name=Name,       @rowguid=rowguid,       @ModifiedDate=ModifiedDate FROM [AdventureWorks].[Person].[AddressType] WHERE (AddressTypeID = @AddressTypeID) 

Implementing Pessimistic and Optimistic Concurrency for Update Operations

The same issues exist for update operations as for delete operations. Refer to Chapter 11, Deleting Data from Microsoft SQL Server 2005, to refresh your knowledge about the management of pessimistic and optimistic concurrency.