Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic creation of Insert, Update, Delete Stored procedures

By Deepthi Viswanathan Nair, (first published: 2009/07/23)

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 From sysobjects , syscolumns , systypes
Where sysobjects.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 
SELECT t.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
WHERE i.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.

Resources:

Dynamic_SP_Creation.sql
Total article views: 32240 | Views in the last 30 days: 6
 
Related Articles
FORUM

To Create PDF from SQL stored procedure

To Create PDF from SQL stored procedure

FORUM

how to run stored procedure from C# application

how to run stored procedure from C# application

FORUM

Creating view from stored procedure results

Creating view from stored procedure results

FORUM

how to run a stored procedure from C# application

how to run a stored procedure from C# application

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones