Agility – how to autogenerate stored procedures for CRUDs (SQL Server)?

One of the most frequently tasks that we face every day as developers is to generate the stored procedures for CRUDs operations over our tables.

I am sharing a script taken from here. What exactly does it do? It generates the stored procedures for CRUD operations over one or all tables of a database, in seconds. The main advantages are:

-          It is a script, simple, clear :). It focuses on SP generation and period. It does what it promises very well.

-          It does not require to install anything, as it is only a script.

-          It concentrates on the database, hence it does not depend on any solution, framework or platform.

-          Easily customizable, ie.  If we want to change the naming convention. In fact, i could do that easliy :).

-          The generated code is easy to read and worked on SQL 2008 too.

-          It is free :)

Although there are some paid or free tools that allow doing this, the simplicity and portability of this script makes it a strong candidate to use within a project.

Additionally, it does not compete with other tools that promise an ORM (Object Relational Mapping) model, i.e. Entity Framework. Besides, at the end of the day they can be (and it is a good practice) linked to stored procedures which need to be generated too. So, we can use this script for automating that.

Well, the idea of this post is to contribute to agility, speed up our development. Any feedback as well as other proposals are welcomed.

Thanks

PP

-- #########################################################

-- 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