February 18, 2002 at 4:54 am
Hi
I'm trying to write a stored procedure in SQL2000 that takes a variable referencing a column in the ORDER BY clause - rather than duplicate the same query over and over with a different hard coded column name...
I've tried declaring a variable and assigning the name of a column but I get the following error:
Server: Msg 1008, Level 15, State 1, Line 15
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.
Fair enough... So I tried to find out how to reference a column name to a variable but couldn't...
I then tried:
select ID, Name from Retailer
order by (select COL_NAME(OBJECT_ID('RPlanner..Retailer'),1))
and while this ran without errors it didn't sort (and anyway wont let me dynamically change the referenced column with a variable in place of the column number...)
Can anyone help?
Cheers
-bill-
February 18, 2002 at 5:05 am
I believe you're going to have to resort to dynamic SQL. Robert Marda has an article on this:
http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp
The problem that you are running into is the ORDER BY will sort based on the data itself from your (select COL_NAME(OBJECT_ID('RPlanner..Retailer'),1)). It doesn't realize you mean for it to get the column name and then sort the overall result set based on the column. This is a little hard to explain. But maybe an example will make it more clear. Take a look at the following thread. The SELECT statement is returning a single field, the IP address. However, we want to make sure the result set is sorted by octet. We do this by building result sets in the ORDER BY clause which are different from what is actually returned:
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 5:12 am
You also could use a case statement in the order by clause.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
February 18, 2002 at 5:58 am
Thanks - the CASE solution works a treat...
And thanks also for the link to dynamic SQL - I've been looking for a way to build storedprocs on the fly and I wasn't sure how to...
-b-
February 18, 2002 at 10:44 am
FYI. Pls try to avoid dynamic sql if you can. There is often a better solution.
Steve Jones
February 19, 2002 at 4:12 am
I agree with Steve. There is almost always a way to write a query without using dynamic SQL as you have just seen you can use the CASE statement in the ORDER BY instead of using dynamic SQL.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 21, 2002 at 3:46 am
After reading all the stuff I can find on dynamic SQL I aggree that using it is a "bad thing". However, the stored proc I'm currently writing would be too unwieldly if I couldn't build the query on the fly - or I'd need about 30+ virtually identical stored procs to to the job: I've a web based report (connecting to the SQL db using Cold Fusion) where the user can choose to sort (ascending or decending) any of the 12 columns, or aggregate the entire query to sum all the values in the columns (which means removing the ID column from the query). There's also a complication in that depending upon the report the "ID" may be one of 6 different columns.
So either
1) I could build a query on the fly in Cold Fusion
2) I could build the query on the fly within a stored proc
3) I could have a single huge stored proc with loads of IFs in it
4) Or I could have loads of separate stored procs which would still need a controlling stored proc (or Cold Fusion page) to work out which stored proc to run
I decided to go for option 2 for several reasons - to keep the stored proc list relativly simple, to remove as much actual processing from Cold Fusion as possible and the fact that the report will not be run by too many people (the web site is a 5 user Intranet site) who wont mind if the report takes 5 seconds to run instead of 1. But mainly for speed of development and the fact I've about 6 of these reports to write and a deadline looming...
Anyway - thanks to those who replied to my original posting...
Cheers
-b-
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply