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)

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