Post reply

dynamic sql

  • June 17, 2004 at 9:21 am

    #162042

    'SELECT  c1,c2,c3,c4,c5,c6

       INTO #emp_temp1 FROM #emp_temp order by ' +@col_name+' '+@orderby

     

    Whats the error in this and how can do this?

    Thanks.

  • June 17, 2004 at 9:31 am

    #510876

    Not enough info. We don't know how #emp_temp is formed, it structure or where @col_name and @orderby come from.

    I take it @orderby is Desc or ASC, and @col_name is a column name.

    Column name list must be column delimited if multiple. About all I can say from the info given.

  • June 17, 2004 at 9:35 am

    #510879

    declare @sql varchar(1000), @col_name varchar(10), @orderby varchar(10)

    set @col_name = 'column'

    set @orderby = 'desc'

    set @sql = 'SELECT  c1,c2,c3,c4,c5,c6

       INTO #emp_temp1 FROM #emp_temp order by ' +@col_name+' '+@orderby

    print @sql

    exec (@sql)


    Shamless self promotion - read my blog http://sirsql.net

  • June 17, 2004 at 9:44 am

    #510884

    You cannot use normal select statements for dynamic sql. Use exec() or sp_executesql or a combination of both. Check BOL for more info. This is another good site: http://www.sommarskog.se/dynamic_sql.html

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 17, 2004 at 10:08 am

    #510894

    select

    1 c1,

    2 c2,

    3 c3,

    4 c4,

    5 c5,

    6 c6

    into #emp_temp

    declare @sql nvarchar(1000), @col_name varchar(10), @orderby varchar(10)

    set @col_name = 'c1' --this has to be one of the column names

    set @orderby = 'desc'

    set @sql = 'SELECT  c1,c2,c3,c4,c5,c6

       INTO ##emp_temp1 FROM #emp_temp order by ' +@col_name+' '+@orderby

    exec sp_executesql @sql

    select * from ##emp_temp1 --global temp - use ##

    --returns:

    --1 2 3 4 5 6

    [font="Courier New"]ZenDada[/font]

  • June 17, 2004 at 10:10 am

    #510896

    BTW, don't understand why you would select into with an order by?

    [font="Courier New"]ZenDada[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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