Skip to content

Instantly share code, notes, and snippets.

@adamcarr
Created February 17, 2011 08:31
Show Gist options
  • Save adamcarr/831307 to your computer and use it in GitHub Desktop.
Save adamcarr/831307 to your computer and use it in GitHub Desktop.
Generate CRUD stored procedures from tables in schema
-- #########################################################
-- 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 _GetAll)
-- 2) Get a specific record from the table (suffix of _GetById)
-- 3) UPDATE or INSERT (UPSERT) - (suffix of _Save)
-- 4) DELETE a specified row - (suffix of _Delete)
-- e.g. For a table called location the script will create
-- procedure definitions for the following procedures:
-- schema.Location_GetAll
-- schema.Location_GetById
-- schema.Location_Save
-- schema.Location_Delete
-- 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 '_GetAll' 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 databasename
DECLARE @DatabaseName varchar(100)
SET @DatabaseName = 'databasename'
-- 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 = 'Execute'
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 = ''
-- What schema do you want the stored procedures to be created under?
DECLARE @SchemaName varchar(20)
SET @SchemaName = 'dbo'
-- 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'
AND t.TABLE_SCHEMA = @SchemaName
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 nvarchar(4000)
DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000), @EXISTS nvarchar(200)
-- 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 [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + 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 [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + 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
SET @EXISTS = ''
SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
-- _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 NOT ' + @EXISTS + ' 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 [' + @SchemaName + '].[' + @ObjectName + '_GetAll]' + 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 [' + @SchemaName + '].[' + @ObjectName + '_GetById]' + 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 [' + @SchemaName + '].[' + @ObjectName + '_Save]' + 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 [' + @TableSchema + '].[' + @CurrentTable + '] (' + 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 [' + @SchemaName + '].[' + @ObjectName + '_Delete]' + 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 [' + @TableSchema + '].[' + @CurrentTable + ']' + 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
SET @EXISTS = ''
SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
-- _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 NOT ' + @EXISTS + ' 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment