Sorry, that's what I get for not looking more closely at the actual
question.
The only way you'll be able to dynamically specify the ORDER BY column will
be to make the entire query dynamic. Unfortunately, that will also
eliminate any speed advantage you would normally gain through the use of a
stored procedure.
Something like this:
declare @sqlstring varchar(255)
declare @sorter varchar(40)
select @sorter = 'col3'
select @sqlstring = 'select col1, col2, col3 from table1, table2 order by '
+ @sorter
exec @sqlstring
|--------------------------+----------------------------------------------|
|Eric A. Laney |Never go to bed with anybody crazier than you |
|Systems Engineer |are. |
|LAN Optimization Team | |
|Verizon Data Services | |
|Voice: 813.978.4404 | Hartley's Second Law|
|Pager: 888.985.8519 | |
|--------------------------+----------------------------------------------|
Brian
Ferrigno To: SQL <[EMAIL PROTECTED]>
<BFerrigno@va cc:
ndis.com> Subject: RE: Stored Procedure help
2001-10-15
17:10
Please
respond to
sql
Thanks Eric,
I had tried removing the quotes but received an error I did.
The error I got was:
"Error 1008: 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"
Apparently SQL Server stored procedures don't like using variables in the
ORDER BY clause.
Have you ever run across this error? Any other suggestions for this
problem?
Brian
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 15, 2001 4:14 PM
To: SQL
Subject: Re: Stored Procedure help
Don't enclose the variable @OrderBy in quotes. Because it was defined as
type varchar, the db already knows it's a string.
|------------------------+------------------------------------------------|
|Eric A. Laney |You will be where you most desire to be in a |
|Systems Engineer |short while. |
|LAN Optimization Team | |
|Verizon Data Services | |
|Voice: 813.978.4404 | Today's Fortune|
|Pager: 888.985.8519 | |
|------------------------+------------------------------------------------|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
