DDL statements with variables for table and column names

Question

In my stored procedure, I make a temp_tbl and want to add several columns in a cursor or while loop. All works fine the cursor (the creation of a temp_bl but I can´t add the column when the column string is in a varchar variable.

WHILE @@FETCH_STATUS = 0
BEGIN       
SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10))

    ALTER TABLE IVS.tmpBus
        ADD @webadressenrow varchar(500) Null

    fetch next from cur_web into @webadressen
    SET @counter = @counter + 1
END

The code above results in a syntax error, while this code works:

WHILE @@FETCH_STATUS = 0
BEGIN       
SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10))

    ALTER TABLE IVS.tmpBus
     ADD SOMECOLUMNAME varchar(500) Null

    fetch next from cur_web into @webadressen
    SET @counter = @counter + 1
END

Can anybody give me a syntax hint to this small problem?

Answer 1

Generally speaking DDL statements i.e. those that define tables and columns do not accept variables for table or column names.

You can sometimes get around that by preparing the statements but support for prepared DDL is not provided by all database engines.

The following example works in SQL Server 2005, although I would suggest that adding columns dynamically may not be the optimal solution

DECLARE @colname1 VARCHAR(10)
DECLARE @colname2 VARCHAR(10)
DECLARE @sql VARCHAR(MAX)

SET @colname1 = 'col1'
SET @colname2 = 'col2'

SET @sql = 'CREATE TABLE temptab (' + @colname1 + ' VARCHAR(10) )'

EXEC (@sql)

INSERT INTO temptab VALUES ('COl 1')


SET @sql = 'ALTER TABLE temptab ADD ' + @colname2 + ' VARCHAR(10)'

EXEC (@sql)

INSERT INTO temptab VALUES ('Col1', 'Col2')

SELECT * FROM temptab

DROP TABLE temptab

Produced the following results

col1       col2
---------- ----------
COl 1      NULL
Col1       Col2

Answer 2

You won't be able to parameterise the ALTER TABLE statement but you could build up the SQL and execute it something like this:

declare @sql nvarchar(max)
set @sql = 'create table IVS.tmpBus ( '

select
    @sql = @sql + 'Webadresse_' +
        row_number() over ( order by col ) +
        ' varchar(500) null, '
from sourceData

set @sql = substring(@sql, 1, len(@sql) - 2) + ' )'
exec @sql

Be careful about security/SQL-Injection attacks though.

Answer 3

Thanks for the Tips, today i want test and Understand the 2 suggested solutions. I wat tell you the Core thing about this non optimal Solution. Maybe we can Cut this Problem on root.

TableA
ID PK
Name Varchar(max)


TableB
ID PK
Adresse
FK_ID_Name

So the Relationship is TableA 1-------->N TableB

When TableB for example has 2 Adresses withe the Same FK_ID_NAME

TableB
ID    Adresse         FK_ID_Name
1     www.abc.de      1
2     www.cia.gov     1

When i do the folowing statement.

SELECT 
TableA.name,
TableB.Adresse 
FROM
TableA
INNER JOIN TableB ON (TableA.ID = TableB.FK_ID_Name)
WHERE
TableB.ID = 1

With this Select the Output are 2 Rows. I Want only one Row with the Adresses in several Columns. So my Head Said that i must do this with a temp Table and Cursor While Loop. Is there an Important Failure so i want to fix this in my head :-)

Thanks a lot.

Answer 4

i have Solved The Problem with the Non optimal Way. The Code Works prefectly for me. i Hope another frustrated programmer can Use this.

DECLARE cur_web CURSOR FOR
SELECT IVS.LG_Webadressen.Adresse FROM IVS.LG_Webadressen WHERE IVS.LG_Webadressen.FK_GID = @welche 

open cur_web /*Cursor wird geöffnet*/
fetch next from cur_web into @webadressen /*Erster Datensatz wird geholt*/

WHILE @@FETCH_STATUS = 0 /*Solange eine Datensatz vorhanden ist*/
BEGIN    
/*Spalte Adden*/
SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(1)) /*Anhängen des     Durchlaufes an den Spaltennamen*/ 
SET @sql = 'ALTER TABLE IVS.temp_tbl ADD ' + @webadressenrow + ' VARCHAR(100)' /*Spalte adden*/
EXEC (@sql)    
/*Wert für die Webadresse wird reingeschrieben*/
SET @sql = 'UPDATE IVS.temp_tbl Set ' + @webadressenrow + ' = ''' + @webadressen + ''' WHERE GID = ' + CAST(@welche as nchar(10)) + ''
EXEC(@sql)
/*nächtser Datensatz wird geholt*/
fetch next from cur_web into @webadressen 
SET @counter = @counter + 1
END
/*Cursor zerstören und Schließen*/
CLOSE cur_web
DEALLOCATE cur_web