-
Variables in Order By
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' 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 = 'corp' and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort
-
Variables in Order By (reply)
------------
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 = '%'
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' 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 = 'corp' and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort
-
Variables in Order By (reply)
You'd think you could do this, but you can't. Here's what I'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 = 'corp' 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 = 'corp' and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
set @SQLstring = 'select * from #temp order by ' + @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 = '%'
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' 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 = 'corp' and
register_id like @register_id and
(crdate >= @vchrDateFrom) AND (crdate < DATEADD(day,1,@vchrDateTo))
order by @sort
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|