June 17, 2004 at 9:21 am
'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
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
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)
June 17, 2004 at 9:44 am
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
June 17, 2004 at 10:08 am
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
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