Every Second is IT: July 2012

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

Every Second is IT

Tuesday, July 10, 2012

SQL Script to automatically generate CRUD Stored Procedures

Speed up development by automatically generating commmon stored procedure definitions

Assumptions made by the script

Example output

Conclusion