Technical Article

Automatically generate SUID procs for any table

,

As a database developer, coding the SELECT, UPDATE, INSERT, and DELETE (SUID) stored procedures for each table in your database can be tedious, boring, and time-consuming. For that reason, I created the procedure usp_GenerateProcsForTable, which automatically generates the basic SUID procedures for any given tablename passed in. Your table must have a primary key in order for this to work properly.

This tool is a huge timesaver for developers and DBAs.

The syntax is [EXEC] usp_GenerateProcsForTable [@table_name=] 'MyTable'.

If you want to go a step further and generate SUID procs for your entire database in one stroke........you can execute this proc via the system-supplied proc sp_MSForEachTable; i.e.,

sp_MSForEachTable 'usp_GenerateProcsForTable ''?'''

 

 

 

ALTER PROCEDURE usp_GenerateProcsForTable
    @table_name    SYSNAME

/*
    Created by Cade Bryant.
    Generates the code for creating 
    SELECT, UPDATE, INSERT, and DELETE
    procs for any given table

    Example: EXEC usp_GenerateProcsForTable 'MyTable'
*/
AS

SET NOCOUNT ON

DECLARE @cols                    VARCHAR(8000),
        @parms                    VARCHAR(8000),
        @parms_with_types        VARCHAR(8000),
        @parms_for_where_clause    VARCHAR(8000),
        @parms_for_update        VARCHAR(8000),
        @pk_col                    SYSNAME

--Remove table-owner prefix if it exists (e.g., if proc was executed via sp_MSForEachTable)"
IF CHARINDEX('.', @table_name) > 0
SET @table_name = SUBSTRING(@table_name, CHARINDEX('.', @table_name) + 1, LEN(@table_name))

--remove brackets
SET @table_name = REPLACE(REPLACE(@table_name, '[', ''), ']', '')

--GET PRIMARY KEY FOR TABLE
SELECT    @pk_col = ccu.COLUMN_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON        tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE    tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND        tc.TABLE_NAME = @table_name
AND        ccu.TABLE_NAME = @table_name
                
DECLARE    @temp    TABLE
        (
            sort    INT,
            code    TEXT
        )    

SELECT    @cols = 
        ISNULL(@cols + ',' + CHAR(13) + CHAR(10), '') + 
        CHAR(9) + COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @table_name

SELECT    @parms = 
        ISNULL(@parms + ',' + CHAR(13) + CHAR(10), '') + 
        CHAR(9) + '@' + COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @table_name

SELECT    @parms_with_types = 
        ISNULL(@parms_with_types + ',' + CHAR(13) + CHAR(10), '') + 
        CHAR(9) + '@' + COLUMN_NAME + ' ' +
        UPPER(DATA_TYPE) + CASE
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                AND DATA_TYPE NOT LIKE '%text%'
            THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
            WHEN NUMERIC_PRECISION IS NOT NULL
                AND ISNULL(NUMERIC_SCALE, 0) > 0
                AND DATETIME_PRECISION IS NULL
            THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' +
                     CAST(NUMERIC_SCALE AS VARCHAR) + ')'
            ELSE ''
        END
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @table_name

SELECT    @parms_for_where_clause = 
        ISNULL(@parms_for_where_clause + CHAR(13) + 'AND', '') + 
        CHAR(13) + CHAR(9) + '(' + COLUMN_NAME + '=' + 
        '@' + COLUMN_NAME + ' OR ' +
        '@' + COLUMN_NAME + ' IS NULL) ' 
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @table_name

SELECT    @parms_for_update = 
        ISNULL(@parms_for_update + ',' + CHAR(13), '') + 
        CHAR(9) + COLUMN_NAME + '=' +
        'ISNULL(@' + COLUMN_NAME + ',' + COLUMN_NAME + ')' 
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @table_name

--GENERATE SELECT PROC:
INSERT    @temp
SELECT 1, 'CREATE PROCEDURE usp_' + @table_name + '_Sel'
UNION ALL
SELECT 2, @parms_with_types
UNION ALL
SELECT 3, ''
UNION ALL
SELECT 4, 'AS'
UNION ALL
SELECT 5, ''
UNION ALL
SELECT 6, 'SELECT'
UNION ALL
SELECT 7, @cols
UNION ALL
SELECT 8, CHAR(13) + 'FROM' + CHAR(13) + CHAR(9) + @table_name + CHAR(13)
UNION ALL
SELECT 9, 'WHERE' + CHAR(9) + @parms_for_where_clause
UNION ALL
SELECT 10, ''
UNION ALL
SELECT 11, 'GO' + CHAR(13) + CHAR(13)

--GENERATE INSERT PROC:
INSERT    @temp
SELECT 12, 'CREATE PROCEDURE usp_' + @table_name + '_Ins'
UNION ALL
SELECT 13, @parms_with_types
UNION ALL
SELECT 14, ''
UNION ALL
SELECT 15, 'AS'
UNION ALL
SELECT 16, ''
UNION ALL
SELECT 17, 'INSERT INTO ' + @table_name
UNION ALL
SELECT 18, '(' + CHAR(13) + @cols + CHAR(13) + ')'
UNION ALL
SELECT 19, ''
UNION ALL
SELECT 20, 'SELECT'
UNION ALL
SELECT 21, @parms
UNION ALL
SELECT 22, 'GO' + CHAR(13) + CHAR(13)

--GENERATE UPDATE PROC:
INSERT    @temp
SELECT 23, 'CREATE PROCEDURE usp_' + @table_name + '_Upd'
UNION ALL
SELECT 24, @parms_with_types
UNION ALL
SELECT 25, ''
UNION ALL
SELECT 26, 'AS'
UNION ALL
SELECT 27, ''
UNION ALL
SELECT 28, 'UPDATE ' + CHAR(13) + CHAR(9) + @table_name
UNION ALL
SELECT 29, 'SET' + CHAR(13) + @parms_for_update
UNION ALL
SELECT 30, 'WHERE' + CHAR(13) + CHAR(9) + 
@pk_col + '=' + '@' + @pk_col
UNION ALL
SELECT 31, 'GO' + CHAR(13) + CHAR(13)

--GENERATE DELETE PROC:
INSERT    @temp
SELECT 32, 'CREATE PROCEDURE usp_' + @table_name + '_Del'
UNION ALL
SELECT 33, CHAR(9) + '@' + @pk_col + ' ' +
UPPER(DATA_TYPE) + CASE
    WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
        AND DATA_TYPE NOT LIKE '%text%'
    THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
    WHEN NUMERIC_PRECISION IS NOT NULL
        AND ISNULL(NUMERIC_SCALE, 0) > 0
        AND DATETIME_PRECISION IS NULL
    THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' +
             CAST(NUMERIC_SCALE AS VARCHAR) + ')'
    ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND COLUMN_NAME = @pk_col
UNION ALL
SELECT 34, ''
UNION ALL
SELECT 35, 'AS'
UNION ALL
SELECT 36, ''
UNION ALL
SELECT 37, 'DELETE FROM ' + CHAR(13) + CHAR(9) + @table_name
UNION ALL
SELECT 38, 'WHERE' + CHAR(13) + CHAR(9) + 
@pk_col + '=' + '@' + @pk_col
UNION ALL
SELECT 39, 'GO' + CHAR(13) + CHAR(13)

SELECT        code
FROM        @temp
ORDER BY    sort

Rate

2.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.33 (3)

You rated this post out of 5. Change rating