Introduction

Update: The code has been updated in the discussion. Please read the comments for more information.

It is easy to write Insert, Update and Delete stored procedures for a table, but it may take lots of time and effort to write these basic stored procedure. Especially if you have lots of tables with lots of fields. That time might be better spent on other tasks.

I have written a stored procedure to help do this automatically, and this article explains how it works. This stored procedure, sp_et_create_sps_for_table, automates the creation of stored procedures for a table using the table name as a parameter. It dynamically creates the scripts for insert, update and delete stored procedures and then creates them. It gathers the primary key information of the table and creates the necessary scripts for the where clause of the update & delete stored procedures of the given table.

Dynamic Script Generation

The stored procedure works by getting the table columns from the system tables, which are available in each database. It uses sysobjects, syscolumns, systypes tables to collect all the columns of the table. The sysobjects table have the information about the databse objects including

• the user tables
• the columns of a table can be pulled from syscolumns table
• the datatype of each column can be get from the systypes table , in which xusertype defines the type of the column.

Joining these systems table together and create the query to get all the column information of the table given . The following script, which is part of the stored procedure, is used to pull the table column information in this way.

-- Get all columns & data types for a table 
SELECT distinct sysobjects.name as 'Table', 
     syscolumns.colid ,
      '[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name  as 'ColumnVariable',
systypes.name + 
Case  When  systypes.xusertype in (165,167,175,231,239 ) Then 
         '(' + Convert(varchar(10),syscolumns.length) +')' Else '' end   as 'DataType' ,
'@'+syscolumns.name  + '  ' + systypes.name + 
Case  When  systypes.xusertype in (165,167,175,231,239 ) 
Then '(' + Convert(varchar(10),syscolumns.length) +')' Else '' end as 'ColumnParameter'
Into#tmp_Structure
Fromsysobjects , syscolumns ,  systypes
Wheresysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
Order by syscolumns.colid

Th next step is to get the Primary key information of the table since they are usually required in the Update and Delete stored produres to identify the rows to update or delete. The system tables sysindexes and sysindexkeys maintain the primary key information for the tables. We need to join sysindexes and sysindexkeys with the other system tables we have used to pull the column information ie, sysobjects, syscolumns and systypes in order to get the primary key information for the table given.

The scripts below shows how to gather all the primary key information of the table and store it into temporary table for further script generation code. In this code sysindexes. status = 2048 represents index used to enforce PRIMARY KEY constraint.

-- Get all Primary KEY columns & data types for a table 
SELECTt.name as 'Table', 
c.colid ,
'[' + c.name + ']' as 'ColumnName',
'@'+c.name  as 'ColumnVariable',
systypes.name + 
Case  When  systypes.xusertype in (165,167,175,231,239 )
 Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'DataType' ,
'@'+c.name  + '  ' + systypes.name + 
Case  When  systypes.xusertype in (165,167,175,231,239 )
 Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'ColumnParameter'
Into#tmp_PK_Structure
FROM    sysindexes i, sysobjects t, sysindexkeys k, syscolumns 
c, systypes
WHEREi.id = t.id AND
i.indid = k.indid  AND i.id = k.ID And
c.id = t.id    AND c.colid = k.colid AND  
i.indid BETWEEN 1 And 254  AND 
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID(@tblName)

In the above code @tblName is the name of the table for which the stored procedures are to be created. I have used a cursor to loop through the temporary tables to create parameter list and table columns to create the dynamic script of stored procedures.

The code checks whether the stored procedure exists in the database or not. If so, the output of this stored procedure shows the error message if it already exists. Otherwise the stored procedure would be creating by executing the dynamic script by EXEC command as shown in the below code.

Exec(@strSPText);

How to use the procedure

This section shows how this stored procedure is working for a table in your database. Create a sample table called "ET_APPLICATION" with these fields. The below script creates the table.

CREATE TABLE [dbo].[ET_APPLICATION](
 [APPLICATION_ID] [int] NOT NULL,
 [APPLICATION_NAME] [varchar](50)  NULL,
 [APP_CATEGORY_ID] [int] NULL,
 [AUDIT_USERID] [varchar](50)  NULL,
 [AUDIT_DATETIME] [datetime] NULL,
 CONSTRAINT [PK_ET_APPLICATION] PRIMARY KEY CLUSTERED 
(
 [APPLICATION_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

In this table APPLICATION_ID is the primary key. Now you can create the INSERT, UPDATE & DELETE stored procedures for the ET_APPLICATION table by executing the stored procedure as shown below.

Exec [dbo].[sp_et_create_sps_for_table] 'ET_APPLICATION'

This will create the script of the stored procedures and execute it as explained above. The output of the stored procedure displays the following message if the insert, update & delete stored procedure have been generated successfully.

Procedure [dbo].[sp_et_application_insert] Created Successfully
Procedure [dbo].[sp_et_application_update] Created Successfully
Procedure [dbo].[sp_et_application_delete] Created Successfully

Also output of the stored procedure displays the error messages if the stored procedures already exist in the database.

Sorry!! [dbo].[sp_et_application_insert] Already exists in the database.
Sorry!! [dbo].[sp_et_application_update] Already exists in the database.
Sorry!! [dbo].[sp_et_application_delete] Already exists in the database.

In this case, you can drop the stored procedures with following commands and create again by executing the stored procedure.

drop procedure [dbo].[sp_et_application_insert]
drop procedure [dbo].[sp_et_application_update]
drop procedure [dbo].[sp_et_application_delete]
Exec [dbo].[sp_et_create_sps_for_table] 'ET_APPLICATION'.

The stored procedures created by this looks like the below code.

/*-- =============================================
-- Author : dbo
-- Create date : Jun 3 2009 2:11PM
-- Description : Insert Procedure for ET_APPLICATION
-- Exec [dbo].[sp_et_application_insert] [APPLICATION_ID],[APPLICATION_NAME],[APP_CATEGORY_ID],[AUDIT_USERID],[AUDIT_DATETIME]
-- ============================================= */
CREATE PROCEDURE [dbo].[sp_et_application_insert]
 @APPLICATION_ID int
 ,@APPLICATION_NAME varchar(50)
 ,@APP_CATEGORY_ID int
 ,@AUDIT_USERID varchar(50)
 ,@AUDIT_DATETIME datetime

AS
BEGIN
 INSERT INTO [dbo].[ET_APPLICATION]
 ( 
 [APPLICATION_ID]
 ,[APPLICATION_NAME]
 ,[APP_CATEGORY_ID]
 ,[AUDIT_USERID]
 ,[AUDIT_DATETIME]

 )
 VALUES
 (
 @APPLICATION_ID
 ,@APPLICATION_NAME
 ,@APP_CATEGORY_ID
 ,@AUDIT_USERID
 ,@AUDIT_DATETIME

 )
END
/*-- =============================================
-- Author      : dbo
-- Create date : Jun  3 2009  2:11PM
-- Description : Update Procedure for ET_APPLICATION
-- Exec [dbo].[sp_et_application_update] [APPLICATION_ID],[APPLICATION_NAME],[APP_CATEGORY_ID],[AUDIT_USERID],[AUDIT_DATETIME]
-- ============================================= */
ALTER PROCEDURE [dbo].[sp_et_application_update]
     @APPLICATION_ID  int
    ,@APPLICATION_NAME  varchar(50)
    ,@APP_CATEGORY_ID  int
    ,@AUDIT_USERID  varchar(50)
    ,@AUDIT_DATETIME  datetime

AS
BEGIN
    UPDATE [dbo].[ET_APPLICATION]
    SET 
         [APPLICATION_ID] = @APPLICATION_ID
        ,[APPLICATION_NAME] = @APPLICATION_NAME
        ,[APP_CATEGORY_ID] = @APP_CATEGORY_ID
        ,[AUDIT_USERID] = @AUDIT_USERID
        ,[AUDIT_DATETIME] = @AUDIT_DATETIME

    WHERE [APPLICATION_ID] = @APPLICATION_ID
END
/*-- =============================================
-- Author      : dbo
-- Create date : Jun  3 2009  2:11PM
-- Description : Delete Procedure for ET_APPLICATION
-- Exec [dbo].[sp_et_application_delete] @APPLICATION_ID  int

-- ============================================= */
ALTER PROCEDURE [dbo].[sp_et_application_delete]
     @APPLICATION_ID  int

AS
BEGIN
    DELETE FROM [dbo].[ET_APPLICATION]
    WHERE [APPLICATION_ID] = @APPLICATION_ID
END

You can download the code and test yourself !! Enjoy scripting...

Update: The code has been updated in the discussion. Please read the comments for more information.

Conclusion

The dynamic generation of basic stored procedure to helps to save time and effort to create the stored procedures manually, especially if you have a large number of tables in your database. It also very flexible and can be customized to meet your needs. It also helps to re-create the stored procedures after change have been made to the table.