Which of course removes one of the benefits of using a sproc - dynamic sql
statements cannot be optimised by SQL for optimum execution speed.
I tried to do this myself before now, and it is possible using case
statements to get some sort of ordering, but I found this to be extremely
unreliable - for some reason it seemed to think that my orderby field had to
be smalldatetime - worked ok on small integers and dates - but text and
larger int fields caused problems.
A possible solution (depending on the number of different fields you may
wish to sort by) is to create a query for each sort order you wish to use,
and put each one within an if block:
IF (@sortfield = 'orderValue')
BEGIN
select * from mytable ORDER BY orderValue;
END
ELSE IF (@sortfield = 'Description')
BEGIN
select * from mytable ORDER BY Description;
END
this obviously creates more work if the query needs to be modified in the
future, and becomes unmangeable if there are too many possible sort fields
(In the procedure I was creating before, I had 10 possible sort fields * 2
directions - 20 queries - which ruled out this option in my case...)
At the end of the day it depends what your priorities are...
HTH
Dan
-----Original Message-----
From: Colin Robinson [mailto:[EMAIL PROTECTED]]
Sent: 16 October 2001 09:27
To: SQL
Subject: RE: Stored Procedure help
Write the sql to a variable first i.e.
SELECT @mydynamicsql = 'SELECT * FROM mytable ORDER BY '+@orderbyvalue
EXECUTE (@mydynamicsql)
> -----Original Message-----
> From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
> Sent: 15 October 2001 22:11
> To: SQL
> Subject: RE: Stored Procedure help
>
>
> 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
