Encapsulating Insert Operations in Stored Procedures

Creating INSERT sentences can be difficult if you must deal with all of the datatype conversions and differences between the globalization configuration in the database and the client workstation. A good example is evidenced when using the datetime datatype. Typically, the database server uses the U.S. date/time format; yet, in our global environment, the user could be in Latin America or Japan. Even though the database expects dates in the mm-dd-yyyy format, the Latin American user will send dates in the dd-mm-yyyy format and the Japanese user will send dates in the yyyy.mm.dd format.

However, more importantly, you must secure your database. Creating insert sentences dynamically can be a security risk. Therefore, a database administrator would prefer to secure data tables by denying write permissions on the tables. These are two good reasons to manage insert operations through stored procedures.

The syntax used to insert data through stored procedures is similar to the syntax used in a simple script. You merely have to use parameters instead of values.

 CREATE PROCEDURE ProductCategory_Insert    @Name nvarchar(50) AS INSERT INTO [AdventureWorks].[Production].[ProductCategory] (    [Name] )      VALUES (    @Name ) 

Notice that you must specify the datatype for the parameters as well as the length in some situations. In addition, you can manage some value replacement inside the stored procedure.

Since the parameters for stored procedures can accept NULL values, you can replace them with the values you want or apply some basic business rules inside the stored procedure.

 CREATE PROCEDURE ProductCategory_Insert    @Name nvarchar(50),    @ModifiedDate datetime =null AS -- If the @modifiedDate parameter receives a null value -- it is replaced with the actual date in the database server SET @ModifiedDate=isnull(@ModifiedDate,getdate()) INSERT INTO [AdventureWorks].[Production].[ProductCategory] (    [Name],    [ModifiedDate] )      VALUES (    @Name,    @ModifiedDate ) 

Moreover, you can encapsulate several insert operations to different tables in the same stored procedure to accomplish business rule requirements.

 CREATE PROCEDURE CategorySubCategory_Insert    @CategoryName nvarchar(50),    @SubCategoryName nvarchar(50) AS -- Insert the new category INSERT INTO [AdventureWorks].[Production].[ProductCategory] (    [Name] )      VALUES (    @Name ) DECLARE @CategoryID INT -- Get the CategoryId for the recent inserted row SET @CategoryId=IDENT_CURRENT('Production.ProductCategory') -- Insert the SubCategory INSERT INTO [AdventureWorks].[Production].[ProductSubcategory]    (       [ProductCategoryID]        ,[Name]    ) VALUES    (        @CategoryInd        ,@SubCategoryName    ) 

Note 

IDENT_CURRENT is a SQL function that retrieves the newest value for an identity column in the table that you use as an argument. You can obtain the same value using @@IDENTITY , but this function returns the last identity generated, which can come from some other table. This can occur if the insert operation executes an INSTEAD OF trigger.

Because some columns in your table can have default values, you can retrieve the final values stored in your record by using parameters to obtain the results. You must define the parameters as output and assign values to them from the newly inserted record before you complete the execution of the stored procedure.

 CREATE PROCEDURE ProductCategory_Insert2    @CategoryId int=null OUTPUT    ,@Name nvarchar(50)=null OUTPUT    ,@RowGuid uniqueidentifier=null OUTPUT    ,@ModifiedDate datetime=null OUTPUT AS -- Insert a new row INSERT INTO [AdventureWorks].[Production].[ProductCategory]            ([Name] )      VALUES            (@Name ) -- Retrieve the values for the inserted row SELECT @CategoryID=CategoryId       ,@Name=Name       ,@RowGuid=rowguid       ,@ModifiedDate=ModifiedDate FROM Production.ProductCategory WHERE CategoryId=IDENT_CURRENT('Production.ProductCategory')