How To Create dynamic column in Sql

Question

Post reply

How To Create dynamic column in Sql

shalini_pdi

Say Hey Kid

Points: 708
More actions
September 18, 2008 at 12:56 am

#198295

Hi,
I have got the situation to add column name dynamically with the data type of int. I have tried to create Stored Procedure with the column name as parameter. My query as follows
Declare @ColumnName as Varchar(50)
Set @ColumnName ='column1'
Alter table shalinitest Add @ColumnName int
I got the following error
Incorrect syntax near '@ColumnName'.
I don't know whether i m moving in a right path. Could anyone Help me

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

shalini_pdi Say Hey Kid Points: 708 More actions · Answer 1

Hi,

I have tried with the modified query as follows

Declare @ColumnName as Varchar(50)

declare @ssql nvarchar(200)

Set @ColumnName ='Column1'

set @ssql = 'Alter table shalinitestAdd ' + @ColumnName + ' int'

But the column is not added in my table

shalini_pdi Say Hey Kid Points: 708 More actions · Answer 2

Hi,

I m sorry. I have missed sp_executesql . Thanks. Working fine

shalini_pdi Say Hey Kid Points: 708 More actions · Answer 3

shalini_pdi (9/18/2008)
Hi,
I m sorry. I have missed sp_executesql . Thanks. Working fine

Is It Possible to pass the data type dynamically

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

shalini_pdi (9/18/2008)
shalini_pdi (9/18/2008)
Hi,
I m sorry. I have missed sp_executesql . Thanks. Working fine
Is It Possible to pass the data type dynamically

Why do you need all in Dynamic sql?

Anyway, make sure to read this atricle fully

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

shalini_pdi Say Hey Kid Points: 708 More actions · Answer 5

Wandrag (9/18/2008)
Yip - just replace the "int" with a variable wich you populate with the datatype you want...
ex.
DECLARE @DataType nVarchar(32)
set @DataType = ' Bigint'

Hi

I have tried With the following code as

Declare @ColumnName as Varchar(50)

declare @ssql nvarchar(200)

declare @dataType nvarchar(150)

Set @ColumnName ='Column1'

Set @dataType ='nvarchar(50)'

set @ssql = 'Alter table Emp Add '+ @ColumnName @dataType

exec sp_executesql @ssql

But i got error as follows

Incorrect syntax near '@dataType'

135heidari Grasshopper Points: 10 More actions · Answer 6

Declare @ColumnName as Varchar(50)
declare @ssql nvarchar(200)
declare @dataType nvarchar(150)

Set @ColumnName ='Column1'
Set @dataType ='nvarchar(50)'
set @ssql = 'Alter table Emp Add '+ @ColumnName+' '+ @dataType

print @ssql
exec sp_executesql @ssql

it works.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 7

135heidari - Saturday, October 6, 2018 11:08 PM
Declare @ColumnName as Varchar(50)
declare @ssql nvarchar(200)
declare @dataType nvarchar(150)
Set @ColumnName ='Column1'
Set @dataType ='nvarchar(50)'
set @ssql = 'Alter table Emp Add '+ @ColumnName+' '+ @dataType
print @ssql
exec sp_executesql @ssql
it works.

Even after 10 years, so will the SQL Injection that can hit this, especially if the column name comes from something that is public facing.

I'll also state that the OP never posted the reasoning behind doing this, which is usually a faulty reason to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How To Create dynamic column in Sql

Cookies on SQLServerCentral