Speed up development by automatically generating commmon stored procedure definitions
There are numerous times when developing a new web or windows application when you will need
to write some stored procedures for the basic CRUD (Create, Read, Update, Delete) operations for each table in your database.
For instance a table will need a stored procedure defined for Inserting a record into it, deleting a specified record from it,
selecting a specified record from it and selecting all records from it. Writing these Stored Procedures
can be very repetitive and time consuming. Once you are familiar with how to write these procedures it
can become a chore having to mindlessly type them all out.
The SQL script provided in this article generates 4 Stored Procedure definitions for each specified
table(s) in your database. The auto generated Procedure definitions achieve the following:
- 1) List all records in the table (proc name suffix of _lst)
- 2) Get a specific record from the table (proc name suffix of _sel)
- 3) UPDATE or INSERT (UPSERT) a row- (proc name suffix of _ups)
- 4) DELETE a specified row - (proc name suffix of _del)
For a table called 'Location' the script will create procedure definitions for the following procedures:
dbo.udp_Location_lst
dbo.udp_Location_sel
dbo.udp_Location_ups
dbo.udp_Location_del
The stored procedure definitions can either be printed to the screen for you to copy and paste OR
executed to actually create the Stored Procs. The stored proc names are prefixed with udp_ (user defined
procedure) to avoid conflicts with any existing system stored procs.
The script uses the INFORMATION_SCHEMA tables to obtain information about the tables and columns in
the specified database. The INFORMATION_SCHEMA is a SQL-92 standard that can be used in most major database
systems including ORACLE and SQL SERVER.
Assumptions made by the script
This script assumes that the primary key will be used for selecting, updating and deleting an individual
record and that the primary key is the first column in the table. The script also assumes if the primary key is
an integer then it is an IDENTITY (autonumber) field.
This script is not suitable for the link tables in the middle of a many to many relationship.
After the script has run you will need to add an ORDER BY clause into the '_lst' procedures
according to your needs / required sort order.
The script assumes you have set valid values for the config variables config section at the top of the script
You can set the script to run on all user defined tables in your database OR on a single specified table.
The script can be downloaded
here
Example output
The following is the printed output from the script when it was set to run on the 'Customers' table
of the 'Northwind' sample database that is provided with SQL Server 2000.
CREATE PROC [dbo].[udp_Orders_lst]
AS
SET NOCOUNT ON
SELECT [OrderID],
[CustomerID],
[EmployeeID],
[OrderDate],
[RequiredDate],
[ShippedDate],
[ShipVia],
[Freight],
[ShipName],
[ShipAddress],
[ShipCity],
[ShipRegion],
[ShipPostalCode],
[ShipCountry]
FROM Orders
SET NOCOUNT OFF
GO
CREATE PROC [dbo].[udp_Orders_sel]
@OrderID int
AS
SET NOCOUNT ON
SELECT [OrderID],
[CustomerID],
[EmployeeID],
[OrderDate],
[RequiredDate],
[ShippedDate],
[ShipVia],
[Freight],
[ShipName],
[ShipAddress],
[ShipCity],
[ShipRegion],
[ShipPostalCode],
[ShipCountry]
FROM Orders
WHERE [OrderID] = @OrderID
SET NOCOUNT OFF
GO
CREATE PROC [dbo].[udp_Orders_ups]
@OrderID int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15)
AS
SET NOCOUNT ON
IF @OrderID = 0 BEGIN
INSERT INTO Orders (
[CustomerID],
[EmployeeID],
[OrderDate],
[RequiredDate],
[ShippedDate],
[ShipVia],
[Freight],
[ShipName],
[ShipAddress],
[ShipCity],
[ShipRegion],
[ShipPostalCode],
[ShipCountry]
)
VALUES (
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
SELECT SCOPE_IDENTITY() As NewID
END
ELSE BEGIN
UPDATE Orders SET
[CustomerID] = @CustomerID,
[EmployeeID] = @EmployeeID,
[OrderDate] = @OrderDate,
[RequiredDate] = @RequiredDate,
[ShippedDate] = @ShippedDate,
[ShipVia] = @ShipVia,
[Freight] = @Freight,
[ShipName] = @ShipName,
[ShipAddress] = @ShipAddress,
[ShipCity] = @ShipCity,
[ShipRegion] = @ShipRegion,
[ShipPostalCode] = @ShipPostalCode,
[ShipCountry] = @ShipCountry
WHERE [OrderID] = @OrderID
END
SET NOCOUNT OFF
GO
CREATE PROC [dbo].[udp_Orders_del]
@OrderID int
AS
SET NOCOUNT ON
DELETE FROM Orders
WHERE [OrderID] = @OrderID
SET NOCOUNT OFF
GO
You can see that the script formats the procedures nicely making it easy to follow the code in them
and providing a consistent approach to naming conventions and formatting which helps speed up development
time.
Conclusion
Whilst you can generate automatic 'SELECT / INSERT / UPDATE / DELETE statements using SQL Server
Enterprise manager or SQL Server Management Studio these do not script the procedure definitions or
parameter definitions / variable names used in the stored procs. The script provided can save you
a great deal of time when you need to write repetitive CRUD stored procedures. Enjoy!.
With thanks from
http://www.sqlbook.com/SQL-Server/Auto-generate-CRUD-Stored-Procedures-40.aspx
Here's the Script
-- #########################################################
-- Author: www.sqlbook.com
-- Copyright: (c) www.sqlbook.com. You are free to use and redistribute
-- this script as long as this comments section with the
-- author and copyright details are not altered.
-- Purpose: For a specified user defined table (or all user defined
-- tables) in the database this script generates 4 Stored
-- Procedure definitions with different Procedure name
-- suffixes:
-- 1) List all records in the table (suffix of _lst)
-- 2) Get a specific record from the table (suffix of _sel)
-- 3) UPDATE or INSERT (UPSERT) - (suffix of _ups)
-- 4) DELETE a specified row - (suffix of _del)
-- e.g. For a table called location the script will create
-- procedure definitions for the following procedures:
-- dbo.udp_Location_lst
-- dbo.udp_Location_sel
-- dbo.udp_Location_ups
-- dbo.udp_Location_del
-- Notes: The stored procedure definitions can either be printed
-- to the screen or executed using EXEC sp_ExecuteSQL.
-- The stored proc names are prefixed with udp_ to avoid
-- conflicts with system stored procs.
-- Assumptions: - This script assumes that the primary key is the first
-- column in the table and that if the primary key is
-- an integer then it is an IDENTITY (autonumber) field.
-- - This script is not suitable for the link tables
-- in the middle of a many to many relationship.
-- - After the script has run you will need to add
-- an ORDER BY clause into the '_lst' procedures
-- according to your needs / required sort order.
-- - Assumes you have set valid values for the
-- config variables in the section immediately below
-- #########################################################
-- ##########################################################
/* SET CONFIG VARIABLES THAT ARE USED IN SCRIPT */
-- ##########################################################
-- Do we want to generate the SP definitions for every user defined
-- table in the database or just a single specified table?
-- Assign a blank string - '' for all tables or the table name for
-- a single table.
DECLARE @GenerateProcsFor varchar(100)
SET @GenerateProcsFor = 'Orders'
--SET @GenerateProcsFor = ''
-- which database do we want to create the procs for?
-- Change both the USE and SET lines below to set the datbase name
-- to the required database.
USE Northwind
DECLARE @DatabaseName varchar(100)
SET @DatabaseName = 'Northwind'
-- do we want the script to print out the CREATE PROC statements
-- or do we want to execute them to actually create the procs?
-- Assign a value of either 'Print' or 'Execute'
DECLARE @PrintOrExecute varchar(10)
SET @PrintOrExecute = 'Print'
-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix varchar(10)
SET @TablePrefix = 'tbl_'
-- For our '_lst' and '_sel' procedures do we want to
-- do SELECT * or SELECT [ColumnName,]...
-- Assign a value of either 1 or 0
DECLARE @UseSelectWildCard bit
SET @UseSelectWildCard = 0
-- ##########################################################
/* END SETTING OF CONFIG VARIABLE
-- do not edit below this line */
-- ##########################################################
-- DECLARE CURSOR containing all columns from user defined tables
-- in the database
DECLARE TableCol Cursor FOR
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
-- Declare variables which will hold values from cursor rows
DECLARE @TableSchema varchar(100), @TableName varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int
DECLARE @ColumnNameCleaned varchar(100)
-- Declare variables which will track what table we are
-- creating Stored Procs for
DECLARE @CurrentTable varchar(100)
DECLARE @FirstTable bit
DECLARE @FirstColumnName varchar(100)
DECLARE @FirstColumnDataType varchar(30)
DECLARE @ObjectName varchar(100) -- this is the tablename with the
-- specified tableprefix lopped off.
DECLARE @TablePrefixLength int
-- init vars
SET @CurrentTable = ''
SET @FirstTable = 1
SET @TablePrefixLength = Len(@TablePrefix)
-- Declare variables which will hold the queries we are building use unicode
-- data types so that can execute using sp_ExecuteSQL
DECLARE @LIST nvarchar(4000), @UPSERT nvarchar(4000)
DECLARE @SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES varchar(4000)
DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000)
-- open the cursor
OPEN TableCol
-- get the first row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
-- loop through the rows of the cursor
WHILE @@FETCH_STATUS = 0 BEGIN
SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')
-- is this a new table?
IF @TableName <> @CurrentTable BEGIN
-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
-- first add any syntax to end the statement
-- _lst
SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @LIST = @LIST + Char(13)
-- _sel
SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @SELECT = @SELECT + Char(13)
-- UPDATE (remove trailing comma and append the WHERE clause)
SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
-- INSERT
SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
SET @INSERT = @INSERT + @INSERTVALUES
-- _ups
SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
END ELSE BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13)
END
SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13)
SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + Char(13)
-- _del
-- delete proc completed already
-- --------------------------------------------------
-- now either print the SP definitions or
-- execute the statements to create the procs
-- --------------------------------------------------
IF @PrintOrExecute <> 'Execute' BEGIN
PRINT @LIST
PRINT @SELECT
PRINT @UPSERT
PRINT @DELETE
END ELSE BEGIN
EXEC sp_Executesql @LIST
EXEC sp_Executesql @SELECT
EXEC sp_Executesql @UPSERT
EXEC sp_Executesql @DELETE
END
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END
-- update the value held in @CurrentTable
SET @CurrentTable = @TableName
SET @FirstColumnName = @ColumnName
SET @FirstColumnDataType = @DataType
IF @TablePrefixLength > 0 BEGIN
IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN
--PRINT Char(13) + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
END ELSE BEGIN
SET @ObjectName = @CurrentTable
END
END ELSE BEGIN
SET @ObjectName = @CurrentTable
END
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
-- ----------------------------------------------------
-- now start building the procedures for the next table
-- ----------------------------------------------------
-- _lst
SET @LIST = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_lst]' + Char(13)
SET @LIST = @LIST + 'AS' + Char(13)
SET @LIST = @LIST + 'SET NOCOUNT ON' + Char(13)
IF @UseSelectWildcard = 1 BEGIN
SET @LIST = @LIST + Char(13) + 'SELECT * '
END
ELSE BEGIN
SET @LIST = @LIST + Char(13) + 'SELECT [' + @ColumnName + ']'
END
-- _sel
SET @SELECT = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_sel]' + Char(13)
SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'
END
SET @SELECT = @SELECT + Char(13) + 'AS' + Char(13)
SET @SELECT = @SELECT + 'SET NOCOUNT ON' + Char(13)
IF @UseSelectWildcard = 1 BEGIN
SET @SELECT = @SELECT + Char(13) + 'SELECT * '
END
ELSE BEGIN
SET @SELECT = @SELECT + Char(13) + 'SELECT [' + @ColumnName + ']'
END
-- _ups
SET @UPSERT = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_ups]' + Char(13)
SET @UPSERT = @UPSERT + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'
END
-- UPDATE
SET @UPDATE = Char(9) + 'UPDATE ' + @TableName + ' SET ' + Char(13)
-- INSERT -- don't add first column to insert if it is an
-- integer (assume autonumber)
SET @INSERT = Char(9) + 'INSERT INTO ' + @TableName + ' (' + Char(13)
SET @INSERTVALUES = Char(9) + 'VALUES (' + Char(13)
IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')
BEGIN
SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
END
-- _del
SET @DELETE = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_del]' + Char(13)
SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'
END
SET @DELETE = @DELETE + Char(13) + 'AS' + Char(13)
SET @DELETE = @DELETE + 'SET NOCOUNT ON' + Char(13) + Char(13)
SET @DELETE = @DELETE + 'DELETE FROM ' + @TableName + Char(13)
SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + Char(13)
SET @DELETE = @DELETE + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @DELETE = @DELETE + Char(13)
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END
ELSE BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
-- is the same table as the last row of the cursor
-- just append the column
-- _lst
IF @UseSelectWildCard = 0 BEGIN
SET @LIST = @LIST + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
END
-- _sel
IF @UseSelectWildCard = 0 BEGIN
SET @SELECT = @SELECT + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
END
-- _ups
SET @UPSERT = @UPSERT + ',' + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'
END
-- UPDATE
SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + Char(13)
-- INSERT
SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
-- _del
-- delete proc completed already
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'
END
-- fetch next row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
END
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol
-- ------------------------------------------------
-- repeat the block of code from within the cursor
-- So that the last table has its procs completed
-- and printed / executed
-- ------------------------------------------------
-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
-- first add any syntax to end the statement
-- _lst
SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @LIST = @LIST + Char(13)
-- _sel
SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @SELECT = @SELECT + Char(13)
-- UPDATE (remove trailing comma and append the WHERE clause)
SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
-- INSERT
SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
SET @INSERT = @INSERT + @INSERTVALUES
-- _ups
SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
END ELSE BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13)
END
SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13)
SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13)
SET @UPSERT = @UPSERT + Char(13)
-- _del
-- delete proc completed already
-- --------------------------------------------------
-- now either print the SP definitions or
-- execute the statements to create the procs
-- --------------------------------------------------
IF @PrintOrExecute <> 'Execute' BEGIN
PRINT @LIST
PRINT @SELECT
PRINT @UPSERT
PRINT @DELETE
END ELSE BEGIN
EXEC sp_Executesql @LIST
EXEC sp_Executesql @SELECT
EXEC sp_Executesql @UPSERT
EXEC sp_Executesql @DELETE
END
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END
