• 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)
    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 
    
  • 0 Comments:

    Publicar un comentario

    ACCEDE A CODIGO FUENTE GRATIS

    Compartiendo código a través de una plataforma gratuita, compartiendo proyectos y codigo siempre con el lema de Educar es Compartir.

    DIRECCIÓN

    Comercial el Rey, Local B56

    CORREO

    angel@nubeando.com
    soporte@nubeando.com

    TELEFONO

    +591 326 4587

    MOVILES

    +591 710 21440