|
|
The idea of a stored procedure generator is to help the developer reduce the number of scripts that need to be written by using a stored procedure to create other stored procedures based on a template. This is only possible if the logic for all the procedures is the same with the exception of table names and column names. It also helps if there is a specific naming convention used.
For example, let’s assume we have a database with more than 100 tables and we need a stored procedure in the form of [sp_getall <tablename>] that returns all rows for that table. For example, if there is a table called [Order Details], you need to create a stored procedure called [sp_getall Order Details].
You could create all of the stored procedures one by one, but this can be very tedious, so let’s create it using dynamic script instead.
First let’s create our stored procedure generator. We’ll call it [sp_generate getall].
 |
if exists
(select * from dbo.sysobjects
where id = object_id(N'[sp_generate getall]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp_generate getall]
GO
create procedure [sp_generate getall]
/******************************************
Procedure: sp_generate getall
Date: 16-July-2002
Author: Ryan N Payet
Description: Generate stored procdure for given table
Version History
---------------
0.1, 16-July-2002, Ryan N Payet:
Procedure Created
******************************************/
@TableName nvarchar(100)
AS
BEGIN
/*Declare variable to hold script*/
DECLARE @@SQLScript as nvarchar(4000)
/*Declare variable to manage errors*/
DECLARE @@ERRORMSG as varchar(256)
/*Check if table exist*/
if NOT exists
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(@TableName)
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
SET @@ERRORMSG = 'Table "' + @TableName
+ '" does not exist in database'
RAISERROR ( @@ERRORMSG , 16, 1)
RETURN 1
END
/*Build script to drop stored procedure*/
SET @@SQLScript = 'if exists' + char(13)
+ ' (select * from dbo.sysobjects' + char(13)
+ ' where id = object_id(N' + char(39)+ '[sp_getall '
+ @TableName + ']'+ char(39)+ ') and ' + char(13)
+ ' OBJECTPROPERTY(id, N' + char(39) + 'IsProcedure'
+ char(39)+ ') = 1)' + char(13)
+ 'drop procedure [sp_getall ' + @TableName + ']'
+ char(13)
/*Run script to drop stored procedure*/
EXECUTE ( @@SQLScript )
/*Build script to create stored procedure*/
SET @@SQLScript =''
+ 'create procedure [sp_getall ' + @TableName + ']'
+ char(13)
+ '/******************************************'
+ char(13)
+ 'Procedure: [sp_getall ' + @TableName + ']'
+ char(13)
+ 'Date: '+ CONVERT(varchar(20),GETDATE())
+ char(13)
+ 'Author: Ryan N Payet (from Auto generator)'
+ char(13)
+ 'Description: ' + char(13)
+ ' Return all rows for ' + char(13)
+ '******************************************/'
+ char(13)
+ 'AS ' + char(13)
+ 'BEGIN ' + char(13)
+ ' SELECT * FROM ['+ @TableName +']' + char(13)
+ 'END' + char(13)
/*Run script to create stored procedure*/
EXECUTE ( @@SQLScript )
END
GO
|
[sp_generate getall] is a simple stored procedure that, through the use of dynamic script, creates other stored procedures. The generated stored procedures also include comments and the date they are generated.
| Tip |
The maximum size nvarchar can be is 8,000, which means it holds only 4,000 characters. If your dynamic script is more than 4,000 characters long, use more than one variable to hold the scripts and split the scripts among each. When you execute the script, you can do the following: |
EXECUTE (@@SQLScript1 + @@SQLScript2 + @@SQLScript3)
Now that the stored procedure generator is ready, you can move on and write a line that will generate a stored procedure for each table. For example:
... EXECUTE [sp_generate getall] 'Orders' EXECUTE [sp_generate getall] 'Order Details' ...
Or you can be more clever and write a script that will do it for you through the use of a cursor:
|
/*
Create script to generate stored procedure
for all table in database
*/
/*Declare variable to hold table name*/
DECLARE @tableName as nvarchar(100)
/*Declare cursor for Loop though table name*/
DECLARE tablename_Cursor CURSOR FOR
SELECT name FROM dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN tablename_Cursor
/*Get the first table name*/
FETCH NEXT FROM tablename_Cursor
INTO @tableName
/*LOOP until end of cursor*/
WHILE @@FETCH_STATUS = 0
BEGIN
/*GENERATE stored procedure*/
EXECUTE [sp_generate getall] @tableName
/*Get next table name*/
FETCH NEXT FROM tablename_Cursor
INTO @tableName
END
/*CLEAN up*/
CLOSE tablename_Cursor
DEALLOCATE tablename_Cursor
GO
|
This technique is practical if you only have a few tables and stored procedures to write, but it is extremely useful if you need to write a large number of stored procedures with similar logic.
|
|