jueves, 7 de septiembre de 2017

Genera Storeds Procedures CRUD

SET nocount  ON
SET CONCAT_NULL_YIELDS_NULL off;
--*********************************************
--*****Se declaran las variables generales*****
--*********************************************
Declare @TableName varchar(250);
Declare @Value varchar(max);
Declare @Values varchar(max);
Declare @Conditions varchar(max);

--*******************************************
--*****Se declaran variables especificas*****
--*******************************************
Declare @TableAux table(id int identity(1,1), columnname varchar(250) , typecolumn varchar(250), isnullable varchar(5),tamanio varchar(5) );
Declare @i int;
Declare @Count int;
Declare @Columnname varchar(250);
Declare @Columntype varchar(250);
Declare @isnullable varchar(5);
Declare @tamanio varchar(5);

Declare @Param varchar(max);
Declare @ParamUpdate varchar(max);
Declare @Params varchar(max);
Declare @ParamsUpdate varchar(max);
Declare @Columns varchar(max);

--***************************************************************************
--*****Se declaran variables para obtener campos Primary Key de la tabla*****
--***************************************************************************
Declare @TablePK table(id int identity(1,1), columnnamePK varchar(250));
Declare @iPK int;
Declare @CountPK int;
Declare @ColumnnamePK varchar(250);
Declare @ColumntypePK varchar(250);
Declare @tamanioPK varchar(5);

Declare @ParamPK varchar(max);
Declare @ParamsPK varchar(max);
Declare @ColumnsPK varchar(max);


set @TableName = 'H_laboral';
set @i = 1;
set @iPK = 1;


Insert into @TableAux
select column_name,data_type,is_nullable,character_maximum_length from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName;
set @Count = @@ROWCOUNT;

--Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'H_Laboral';

while (@i <= @Count)
begin
 select @Columnname = columnname, @Columntype = typecolumn, @isnullable = isnullable, @tamanio = tamanio from @TableAux where id = @i;

 if @i = 1
    begin
        set @Columns = @Columns + @Columnname
    end
    else
    begin
        set @Columns = @Columns +  CHAR(13) + ',' + @Columnname
    end

 if(@Columntype='varchar')
 begin
     if(@isnullable='YES')
     begin
         if @i = 1
         begin
             set @Value = '@' + @Columnname
             set @Param = '@' + @Columnname  + ' '+ @Columntype +'(' +@tamanio +')' +' = Null'                
             set @ParamUpdate = @Columnname + ' = ' + '@' + @Columnname
         end
         else
         begin
             set @Value = CHAR(13) + '@' + @Columnname
             set @Param =  CHAR(13) + ',@' + @Columnname  + ' '+ @Columntype +'(' +@tamanio +')' +' = Null'
             set @ParamUpdate = CHAR(13) + ',' + @Columnname + ' = ' + '@' + @Columnname
         end
     end
     else
     begin
         if @i =1
         begin
             set @Value = '@' + @Columnname
             set @Param= '@' + @Columnname  + ' ' + @Columntype +'(' +@tamanio +')'
             set @ParamUpdate = @Columnname + ' = ' + '@' + @Columnname
         end
         else
         begin
             set @Value = CHAR(13) + '@' + @Columnname
             set @Param= CHAR(13) +',@' + @Columnname  + ' ' + @Columntype +'(' +@tamanio +')'
             set @ParamUpdate = CHAR(13) + ',' + @Columnname + ' = ' + '@' + @Columnname
         end
     end   
 end   
 else
 begin
     if(@isnullable='YES')
     begin
         if @i = 1
         begin
             set @Value = '@' + @Columnname
             set @Param=  '@' + @Columnname  + ' '+ @Columntype +' = Null'
             set @ParamUpdate = @Columnname + ' = ' + '@' + @Columnname
         end
         else
         begin
             set @Value = CHAR(13) + '@' + @Columnname
             set @Param=  CHAR(13) + ',@' + @Columnname  + ' '+ @Columntype +' = Null'
             set @ParamUpdate = CHAR(13) + ',' + @Columnname + ' = ' + '@' + @Columnname
         end
     end
     else
     begin
         if @i = 1
         begin
             set @Value = '@' + @Columnname
             set @Param= '@' + @Columnname  + ' ' + @Columntype
             set @ParamUpdate = @Columnname + ' = ' + '@' + @Columnname
         end
         else
         begin
             set @Value = CHAR(13) + '@' + @Columnname
             set @Param= CHAR(13) + ',@' + @Columnname  + ' ' + @Columntype
             set @ParamUpdate = CHAR(13) + ',' + @Columnname + ' = ' + '@' + @Columnname
         end
     end
 end

 set @Values = @Values + @Value
 set @ParamsUpdate = @ParamsUpdate + @ParamUpdate
 set @Params = @Params + @Param
 set @i = @i + 1;
end;

--****************************************************
--*****Se obtienen campos Primary Key de la tabla*****
--****************************************************

Insert into @TablePK
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND TABLE_NAME = @TableName --AND TABLE_SCHEMA = 'Schema'
set @CountPK = @@ROWCOUNT;
--SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND TABLE_NAME = 'H_Laboral'

while (@iPK<=@CountPK)
Begin
 select  @ColumnnamePK = columnnamePK
 from @TablePK where id = @iPK;

 Set @tamanioPK = (select character_maximum_length from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and COLUMN_NAME = @ColumnnamePK)
 Set @ColumntypePK = (select data_type from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and COLUMN_NAME = @ColumnnamePK)

  If(@ColumntypePK='varchar')
  begin
    if @iPK = 1
        begin
            set @ColumnsPK = @ColumnsPK + @ColumnnamePK
            set @ParamPK = '@' + @ColumnnamePK + ' ' + @ColumntypePK +'(' + @tamanioPK +')'
        end
        else
        begin
            set @ColumnsPK = @ColumnsPK +  CHAR(13) + ',' + @ColumnnamePK
            set @ParamPK =  CHAR(13) + ',@' + @ColumnnamePK + ' ' + @ColumntypePK +'(' + @tamanioPK +')'
        end
  end;
  Else
  begin
        if @iPK = 1
        begin
            set @ColumnsPK = @ColumnsPK + @ColumnnamePK
            set @ParamPK = '@' + @ColumnnamePK + ' ' + @ColumntypePK
        end
        else
        begin
            set @ColumnsPK = @ColumnsPK +  CHAR(13) + ',' + @ColumnnamePK
            set @ParamPK =  CHAR(13) + ',@' + @ColumnnamePK + ' ' + @ColumntypePK
        end
  end

set @Conditions = @Conditions + CHAR(13) + @ColumnnamePK + ' = ' + '@' + @ColumnnamePK
set @ParamsPK = @ParamsPK + @ParamPK
set @iPK = @iPK + 1;

End

--print @Params;
--print @ParamsUpdate
--print @ParamsPK
--print @Conditions
--print @@Values

print ' '
print '--Insert'
print 'Create Procedure prc_' + @TableName + '_Insert'
print @Params
print 'as'
print 'BEGIN'
print 'SET NOCOUNT ON'
print 'insert into ' + @TableName
print '(' + @Columns + ')'
print 'values('
print @Values
print ')'
print 'select scope_identity();'
print 'END'

-------------------------------------------------------------------------

print ' '
print '--GetOne'
print 'Create Procedure prc_' + @TableName + '_GetOne'
print @ParamsPK
print 'as'
print 'BEGIN'
print 'SET NOCOUNT ON'
print 'select ' + @Columns
print 'from ' + @TableName + ' with(nolock)'
print 'where ' + @Conditions
print 'END'

-------------------------------------------------------------------------

print ' '
print '--GetAll'
print 'Create Procedure prc_' + @TableName + '_GetAll'
print 'as'
print 'BEGIN'
print 'SET NOCOUNT ON'
print 'select ' + @Columns
print 'from ' + @TableName + ' with(nolock)'
print 'END'

-------------------------------------------------------------------------

print ' '
print '--Update'
print 'Create Procedure prc_' + @TableName + '_Update'
print @Params
print 'as'
print 'BEGIN'
print 'SET NOCOUNT ON'
print 'update ' + @TableName + ' set'
print @ParamsUpdate
print 'where ' + @Conditions
print 'END'

-------------------------------------------------------------------------

print ' '
print '--Delete'
print 'Create Procedure prc_' + @TableName + '_Drop'
print @ParamsPK
print 'as'
print 'BEGIN'
print 'SET NOCOUNT ON'
print 'delete from ' + @TableName
print 'where ' + @Conditions

-------------------------------------------------------------------------

--select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='modulo'

No hay comentarios.:

Publicar un comentario