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 7 (of 7 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

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: 1004432 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)