none
Alter table dinámico RRS feed

  • Pregunta

  • 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

    lunes, 6 de agosto de 2018 7:22

Respuestas

  • 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.

    lunes, 6 de agosto de 2018 7:44
  • 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

    lunes, 6 de agosto de 2018 15:59
    Moderador

Todas las respuestas

  • 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.

    lunes, 6 de agosto de 2018 7:44
  • 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

    lunes, 6 de agosto de 2018 15:59
    Moderador