Alter table dinámico

Question

Buenos días,

Estoy preparando un stored procedure en el que quiero crear una tabla temporal con campos dinámicos. Para ello primero creo la tabla temporal con los campos fijos y después hago un cursor para añadir los campos variables. Al poner el nombre del campo con variable el editor ya me marca error de sintaxis, no le gusta que el nombre del campo sea @xxxx ¿Cómo puedo crear campos de forma dinámica?

Ejemplo simplificado:

create table #temp(
ITEM varchar(200),
IDCAL varchar(100)
BRII varchar(30)

)

declare cColumnasTemp cursor for
select U_GISA_CIR from [@GISAC_VIR2] vir2 join [@GISAC_OVIR] vir on vir2.Code=vir.Code  
where vir.U_GISA_PRY=@proyecto and vir.U_GISA_POS = @posicion

declare @cir varchar(20)

open cColumnasTemp
fetch next from cColumnasTemp into @cir, @vir
while (@@fetch_status=0)
begin
alter table #temp add @cir varchar(20)
--print @cir

fetch next from cColumnasTemp into @cir
end
close cColumnasTemp
deallocate cColumnasTemp

Un saludo

Answer 1

No, no puedes usar un "@loquesea" en los nombres de columnas. La única solución es usar SQL dinámico, que consiste en ir concatenando todo el texto del "alter table" en una cadena (de tipo nvarchar), y luego cuando toda la sentencia esté escrita dentro del nvarchar, ejecutarla mediante sp_executesql.

Ojo si haces esto, tienes que validar previamente los nombres de columna (si es que pueden ser introducidos por el usuario) para cerciorarte de que no contienen ninguna inyección de SQL.

Answer 2

con sql dinámico tendrás problemas por el hecho de ser tablas temporales, solo existen en tu conexión, así pues si haces esto por ejemplo

exec ('create table #t (id int)')
select * from #t

veras que dice que no existe el objeto #t

las dos cosas juntas hacen que sea muy complicado conseguir esto con una tabla temporal

se me ocurre lo siguiente usando una tabla global

create table ##temp(
ITEM varchar(200),
IDCAL varchar(100),
BRII varchar(30)

)

declare cColumnasTemp cursor for
select 'a' as col
union all 
select 'b' 
union all 
select 'c'
/*select U_GISA_CIR from [@GISAC_VIR2] vir2 join [@GISAC_OVIR] vir on vir2.Code=vir.Code  
where vir.U_GISA_PRY=@proyecto and vir.U_GISA_POS = @posicion
*/
declare @cir varchar(20)
declare @sql varchar(1000)




open cColumnasTemp
fetch next from cColumnasTemp into @cir
while (@@fetch_status=0)
begin
set @sql='alter table ##temp add ' + @cir + ' varchar(20)'
print @sql
exec(@sql)
fetch next from cColumnasTemp into @cir
end
close cColumnasTemp
deallocate cColumnasTemp

select * from ##temp
drop table ##temp

y esto, que puede tener problemas de concurrencia con una tabla temporal, aunque podrian minimizarse consultando en lugar de information_schema.tables, sys.sysobjects donde la fecha de creación sea este instante...

create table #temp(
ITEM varchar(200),
IDCAL varchar(100),
BRII varchar(30)

)


declare @table_name sysname
select @table_name=table_name  from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME like '#temp%'
select @table_name

declare cColumnasTemp cursor for
select 'a' as col
union all 
select 'b' 
union all 
select 'c'
/*select U_GISA_CIR from [@GISAC_VIR2] vir2 join [@GISAC_OVIR] vir on vir2.Code=vir.Code  
where vir.U_GISA_PRY=@proyecto and vir.U_GISA_POS = @posicion
*/
declare @cir varchar(20)
declare @sql varchar(1000)




open cColumnasTemp
fetch next from cColumnasTemp into @cir
while (@@fetch_status=0)
begin
set @sql='alter table '+ @table_name+ ' add ' + @cir + ' varchar(20)'
print @sql
exec(@sql)
fetch next from cColumnasTemp into @cir
end
close cColumnasTemp
deallocate cColumnasTemp

select * from #temp
drop table #temp

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA