How To Create dynamic column in Sql

  • shalini_pdi

    Say Hey Kid

    Points: 708

    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

  • shalini_pdi

    Say Hey Kid

    Points: 708

    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

    Hi,

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

  • shalini_pdi

    Say Hey Kid

    Points: 708

    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: 7405

    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

    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

    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: 994279

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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