Add columns dynamically in the TSQL stored procedure

Question

Hi, I am writing a large stored procedure that creates a dynamic report table, of n columns in size, the first 6 are constants, the rest depend on a few arguments passed to the procedure to create the table with the necessary columns.

The problem I encountered is related to the following TSQL

DECLARE @columnname VARCHAR(50)
SET @columnname = 'on_' + @description

IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('reports')
       AND NAME = @columnname)
BEGIN
      ALTER TABLE reports ADD @columnname VARCHAR(50) NULL
END

I get syntax errors with this in @columnname in the ALTER TABLE statement of the above code.

Also, since I'm new to this, I'm not sure if this is the best way to do this, or if TSQL has better ways to generate the required dynamic table.

Answer 1

:

@sql nvarchar (100)

set @sql = 'ALTER TABLE ADD' + @columnname + 'VARCHAR (50) NULL'

exec sp_executesql @sql

Answer 2

DECLARE @columnname VARCHAR(50)
SET @columnname = '[on_' + @description +']'
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('reports')       
    AND NAME = @columnname)
BEGIN      
ALTER TABLE reports ADD @columnname VARCHAR(50) NULL
END

Answer 3

It is impossible to get around to doing this dynamically. I believe that change the BEGIN block to something like this:

DECLARE @sql VARCHAR(8000)

BEGIN      
    SET @sql = 'ALTER TABLE Table_1 ADD '+@columnname+' VARCHAR(50) NULL'    
    EXEC(@sql)        
END