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: GIAN CARLOS ALMEYDA' + @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, ISNULL(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
--PRINT @length
SET @spParameters = @spParameters + (CASE WHEN LEN(@spParameters) >0 THEN @nl + ' ,' ELSE ' ' END) + '@' + @column + ' ' + UPPER(@data_type) + (CASE @data_type WHEN 'NVARCHAR' THEN '('+CAST(ISNULL(@length,'') AS VARCHAR)+')' WHEN 'VARCHAR' THEN '('+CAST(ISNULL(@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
Comentarios
Publicar un comentario