Generar automáticamente procedimientos almacenados a partir de una tabla

La creación de procedimientos almacenados para un CRUD puede resultar una tarea bastante repetitiva. Aquí publico un procedimiento almacenado para SQL Server (T-SQL) que sirve para generar un script para crear 4 procedimientos almacenados a partir del nombre de una tabla.

El script creará 4 procedimientos. Todos los procedimientos que crea el script reciben como parámetros todos los campos de la tabla.
- SELECT filtrará por los campos recibidos que no sean NULL (where condicionado)
- INSERT insertará un registro con los parámetros recibidos
- UPDATE tal y como se genera no tiene sentido, simplemente borra las líneas que no quieras (del SET ó del WHERE)
- DELETE borrará registros filtrando por los campos recibidos que no sean NULL
- EXISTS devolverá registros que coincidan con el filtro creado con los campos recibidos que no sean NULL (where condicionado)

Aquí está el script:

CREATE PROCEDURE [dbo].[sp_generate]
  @tableName AS VARCHAR(100)
AS

--CAPITALIZE TABLENAME
SET @tableName = UPPER(LEFT(@tableName,1)) + RIGHT(@tableName, LEN(@tableName) -1)

--SALTO DE LÍNEA
DECLARE @nl AS CHAR
SET @nl = CHAR(10) + CHAR(13) 

--CABECERA
DECLARE @spHeaders AS VARCHAR(1000)
SET @spHeaders = 'SET ANSI_NULLS ON' + @nl +
'GO' + @nl +
'SET QUOTED_IDENTIFIER ON' + @nl +
'GO' + @nl +
'-- =============================================' + @nl +
'-- Author:  TU_NOMBRE' + @nl +
'-- Create date: ' + CONVERT(VARCHAR, GETDATE(), 3) + @nl +
'-- ============================================='

DECLARE @table AS VARCHAR(MAX)
DECLARE @column AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX)
DECLARE @length AS INT
DECLARE @precision AS INT
DECLARE @scale AS INT

--PARÁMETROS
DECLARE @spParameters AS VARCHAR(MAX) SET @spParameters = ''

--LISTA DE CAMPOS
DECLARE @fieldList AS VARCHAR(MAX) SET @fieldList = ''

--LISTA DE CAMPOS PARA EL SET DEL UPDATE
DECLARE @fieldSetList AS VARCHAR(MAX) SET @fieldSetList = ''

--LISTA DE PARÁMETROS PARA EL INSERT
DECLARE @insertParameters AS VARCHAR(MAX) SET @insertParameters = ''

--CONDICIONES
DECLARE @spConditions AS VARCHAR(MAX) SET @spConditions = ''

DECLARE c CURSOR STATIC FOR
select table_name, column_name, data_type, character_maximum_length,numeric_precision, numeric_scale from information_schema.columns where table_name = @tableName order by ordinal_position
OPEN c FETCH NEXT FROM c INTO @table, @column, @data_type, @length, @precision, @scale
WHILE @@FETCH_STATUS = 0 BEGIN

 SET @spParameters = @spParameters + (CASE WHEN LEN(@spParameters) >0 THEN @nl + ' ,' ELSE '  ' END) + '@' + @column + ' ' + UPPER(@data_type) + (CASE @data_type WHEN 'VARCHAR' THEN '('+CAST(@length AS VARCHAR)+')' WHEN 'DECIMAL' THEN '('+CAST(@precision AS VARCHAR)+', '+CAST(@scale AS VARCHAR)+')' ELSE '' END) + ' = NULL'
 SET @fieldList = @fieldList + (CASE WHEN LEN(@fieldList) >0 THEN @nl + '    ,' ELSE '' END) + @column
 SET @spConditions = @spConditions + (CASE WHEN LEN(@spConditions) >0 THEN @nl + '   AND ' ELSE '' END) + '(@' + @column + ' IS NULL OR @' + @column + '=' + @column + ')'
 SET @fieldSetList = @fieldSetList + (CASE WHEN LEN(@fieldSetList) >0 THEN @nl + '     ,' ELSE '      ' END) + @column + ' = @' + @column
 SET @insertParameters = @insertParameters + (CASE WHEN LEN(@insertParameters) >0 THEN @nl + '    ,' ELSE '' END) + '@' + @column

 FETCH NEXT FROM c INTO @table, @column, @data_type, @length, @precision, @scale
END
CLOSE c DEALLOCATE c

--********************************
--*********** SELECT *************
--********************************
DECLARE @SELECT AS VARCHAR(MAX)
SET @SELECT = @spHeaders + @nl
SET @SELECT = @SELECT + 'CREATE PROCEDURE ' + @tableName + '_Select' + @nl
SET @SELECT = @SELECT + @spParameters + @nl
SET @SELECT = @SELECT + 'AS' + @nl + ' SET NOCOUNT OFF;' + @nl + @nl
SET @SELECT = @SELECT + '    SELECT ' + @fieldList + @nl
SET @SELECT = @SELECT + '    FROM ' + @table + @nl
SET @SELECT = @SELECt + '    WHERE ' + @spConditions + @nl

--********************************
--*********** UPDATE *************
--********************************
DECLARE @UPDATE AS VARCHAR(MAX)
SET @UPDATE = @spHeaders + @nl
SET @UPDATE = @UPDATE + 'CREATE PROCEDURE ' + @tableName + '_Update' + @nl
SET @UPDATE = @UPDATE + @spParameters + @nl
SET @UPDATE = @UPDATE + 'AS' + @nl + ' SET NOCOUNT OFF;' + @nl + @nl
SET @UPDATE = @UPDATE + '    UPDATE ' + @table + ' SET ' + @nl
SET @UPDATE = @UPDATE + @fieldSetList + @nl
SET @UPDATE = @UPDATE + '    WHERE ' + @spConditions + @nl

--********************************
--*********** DELETE *************
--********************************
DECLARE @DELETE AS VARCHAR(MAX)
SET @DELETE = @spHeaders + @nl
SET @DELETE = @DELETE + 'CREATE PROCEDURE ' + @tableName + '_Delete' + @nl
SET @DELETE = @DELETE + @spParameters + @nl
SET @DELETE = @DELETE + 'AS' + @nl + ' SET NOCOUNT OFF;' + @nl + @nl
SET @DELETE = @DELETE + '    DELETE FROM ' + @table + @nl
SET @DELETE = @DELETE + '    WHERE ' + @spConditions + @nl

--********************************
--*********** INSERT *************
--********************************
DECLARE @INSERT AS VARCHAR(MAX)
SET @INSERT = @spHeaders + @nl
SET @INSERT = @INSERT + 'CREATE PROCEDURE ' + @tableName + '_Insert' + @nl
SET @INSERT = @INSERT + @spParameters + @nl
SET @INSERT = @INSERT + 'AS' + @nl + ' SET NOCOUNT OFF;' + @nl + @nl
SET @INSERT = @INSERT + '    INSERT INTO ' + @table + '(' + @nl
SET @INSERT = @INSERT + '     ' + @fieldList + @nl
SET @INSERT = @INSERT + ' )' + @nl + ' VALUES(' + @nl + '     ' + @insertParameters + @nl
SET @INSERT = @INSERT + ' )' + @nl

--********************************
--*********** EXISTS *************
--********************************
DECLARE @EXISTS AS VARCHAR(MAX)
SET @EXISTS = @spHeaders + @nl
SET @EXISTS = @EXISTS + 'CREATE PROCEDURE ' + @tableName + '_Exists' + @nl
SET @EXISTS = @EXISTS + @spParameters + @nl
SET @EXISTS = @EXISTS + ' ,@exists BIT OUT' + @nl
SET @EXISTS = @EXISTS + 'AS' + @nl + ' SET NOCOUNT OFF;' + @nl + @nl
SET @EXISTS = @EXISTS + '    IF EXISTS (' + @nl + ' SELECT ' + LEFT(@fieldList,CHARINDEX(@nl,@fieldList))
SET @EXISTS = @EXISTS + '    FROM ' + @table + @nl
SET @EXISTS = @EXISTS + '    WHERE ' + @spConditions + @nl + ' )' + @nl
SET @EXISTS = @EXISTS + ' SET @exists = 1' + @nl + ' ELSE SET @exists = 0'

--MOSTRAR GENERADOS
PRINT + '-- =====INSERT==================================' + @nl + @INSERT
PRINT + '-- =====DELETE==================================' + @nl + @DELETE
PRINT + '-- =====UPDATE==================================' + @nl + @UPDATE
PRINT + '-- =====SELECT==================================' + @nl + @SELECT
PRINT + '-- =====EXISTS==================================' + @nl + @EXISTS

Se usa así:

sp_generate NOMBRE_DE_TABLA