TSQL dynamic adding of columns in stored procedure

Question

Hi I am writing a large stored procedure, which creates a dynamic report table, of n columns in size, the first 6 are constant the remainder depend on a few arguments passed to the procedure to create the table with the required columns.

The problem that I am having is with 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 am getting syntax errors with this at the @columnname in the ALTER TABLE statement of the above code.

Also as I am new to this, I am not sure if this is the best way to do this, or if there are better ways in TSQL to generate the required dynamic table.

Community · Accepted Answer · 2020-06-20 09:12:55Z

13

Try this:

declare @sql nvarchar(100)

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

exec sp_executesql @sql

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 5, 2009 at 13:52

SqlACID

4,02423 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

itchi · Accepted Answer · 2011-05-05 18:51:47Z

3

Try

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

edited May 5, 2011 at 18:51

itchi

1,71314 silver badges30 bronze badges

answered Nov 3, 2010 at 5:58

Roy Goh

311 bronze badge

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-11-22 08:15:16Z

3

Cannot get around having to do it dynamically I believe so change your 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

edited Nov 22, 2013 at 8:15

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

answered Jun 5, 2009 at 13:56

Christopher Klein

2,8035 gold badges40 silver badges65 bronze badges

Collectives™ on Stack Overflow

TSQL dynamic adding of columns in stored procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related