Variables in Order By

[Randy Friend]

I have a stored procedure (see below) and would like to be able to pass a variable for the sort order, however, when I do I receive the following error message. Any ideas on how I can get around this?

Thanks,
Randy

---Error Message---
Server: Msg 1008, Level 15, State 1, Line 24
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

---Stored Procedure---
declare @store_id varchar(15)
declare @register_id varchar(15)
declare @vchrDateFrom varchar(10)
declare @vchrDateTo varchar(10)
declare @sort varchar(10)

set @store_id = '%'
set @register_id = '%'
set @vchrDateFrom = '03/01/2002'
set @vchrDateTo = '05/15/02'
set @sort = 'store_id'

select store_id, register_id, errnum, errmsg1, errmsg2, progerr, progmast, crdate
from vpl_syserr_log
where (store_id like @store_id or store_id = 'corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
union all
select store_id, register_id, errnum, errortext errmsg1, odbctext errmsg2, odbcstate progerr, convert(char(15), errnumodbc) progmast, crdate
from vpl_odbcerr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort

[Bill]

------------
Randy Friend at 5/10/2002 12:45:56 PM

I have a stored procedure (see below) and would like to be able to pass a variable for the sort order, however, when I do I receive the following error message. Any ideas on how I can get around this?

Thanks,
Randy

---Error Message---
Server: Msg 1008, Level 15, State 1, Line 24
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

---Stored Procedure---
declare @store_id varchar(15)
declare @register_id varchar(15)
declare @vchrDateFrom varchar(10)
declare @vchrDateTo varchar(10)
declare @sort varchar(10)

set @store_id = &#39;%&#39;
set @register_id = &#39;%&#39;
set @vchrDateFrom = &#39;03/01/2002&#39;
set @vchrDateTo = &#39;05/15/02&#39;
set @sort = &#39;store_id&#39;

select store_id, register_id, errnum, errmsg1, errmsg2, progerr, progmast, crdate
from vpl_syserr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
union all
select store_id, register_id, errnum, errortext errmsg1, odbctext errmsg2, odbcstate progerr, convert(char(15), errnumodbc) progmast, crdate
from vpl_odbcerr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort

[Bill]

You&#39;d think you could do this, but you can&#39;t. Here&#39;s what I&#39;d do:

declare @SQLstring varchar(2000)

select store_id, register_id, errnum, errmsg1, errmsg2, progerr, progmast, crdate
INTO #TEMP
from vpl_syserr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
union all
select store_id, register_id, errnum, errortext errmsg1, odbctext errmsg2, odbcstate progerr, convert(char(15), errnumodbc) progmast, crdate
from vpl_odbcerr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))

set @SQLstring = &#39;select * from #temp order by &#39; + @sort
exec(@SQLstring)

drop table #temp

------------
Randy Friend at 5/10/2002 12:45:56 PM

I have a stored procedure (see below) and would like to be able to pass a variable for the sort order, however, when I do I receive the following error message. Any ideas on how I can get around this?

Thanks,
Randy

---Error Message---
Server: Msg 1008, Level 15, State 1, Line 24
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

---Stored Procedure---
declare @store_id varchar(15)
declare @register_id varchar(15)
declare @vchrDateFrom varchar(10)
declare @vchrDateTo varchar(10)
declare @sort varchar(10)

set @store_id = &#39;%&#39;
set @register_id = &#39;%&#39;
set @vchrDateFrom = &#39;03/01/2002&#39;
set @vchrDateTo = &#39;05/15/02&#39;
set @sort = &#39;store_id&#39;

select store_id, register_id, errnum, errmsg1, errmsg2, progerr, progmast, crdate
from vpl_syserr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
union all
select store_id, register_id, errnum, errortext errmsg1, odbctext errmsg2, odbcstate progerr, convert(char(15), errnumodbc) progmast, crdate
from vpl_odbcerr_log
where (store_id like @store_id or store_id = &#39;corp&#39 and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort