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