dynamic sorting in stored procedure

dynamic sorting in stored procedure

Post by James Dyke » Thu, 23 Aug 2001 20:22:50



I need to write a stored procedure that will take an input parameter as the
sort order (ORDER BY clause). SQL Server demands that the ORDER BY clause
refer directly to one of the columns in the SELECT clause, so I can't just
put the variable into the order by clause. Any suggestions?
Thanks . . .
 
 
 

dynamic sorting in stored procedure

Post by Tobias Thernstr? » Thu, 23 Aug 2001 20:32:29


I the different columns you are sorting on have the same base-datatype (ex.
int) you can use the CASE-function.

.. ORDER BY CASE .... END

If not try dynamic SQL with the EXEC-statement.

/ Tobias

 
 
 

dynamic sorting in stored procedure

Post by gert » Thu, 23 Aug 2001 21:05:08





Quote:>-----Original Message-----
>I need to write a stored procedure that will take an

input parameter as the
Quote:>sort order (ORDER BY clause). SQL Server demands that the
ORDER BY clause
>refer directly to one of the columns in the SELECT

clause, so I can't just
Quote:>put the variable into the order by clause. Any
suggestions?
>Thanks . . .

>.

 
 
 

dynamic sorting in stored procedure

Post by Doo » Thu, 23 Aug 2001 23:50:49


Every book/article you read on SS performance tells you NOT to use dynamic
SQL.  But that solution is constantly offered up.  There are various ways
around this problem WITHOUT using dynamic SQL.  One was listed already.
Another is to use parameter(s) and control-of-flow language in the SP to run
the desired routine.  If performance is not a concern then use wild SQL.  If
it is, or you have a limited budget for hardware, avoid it like the plague.

--
Doo
~~~~~~~~~~~~~~~~
Sr. Data Architect / DBA
PlanetJam Media Group

~~~~~~~~~~~~~~~~

Quote:> I need to write a stored procedure that will take an input parameter as
the
> sort order (ORDER BY clause). SQL Server demands that the ORDER BY clause
> refer directly to one of the columns in the SELECT clause, so I can't just
> put the variable into the order by clause. Any suggestions?
> Thanks . . .

 
 
 

dynamic sorting in stored procedure

Post by Wallace Cast » Fri, 24 Aug 2001 07:46:57


The following example is base on the SQL Server 2000 Books Online articles.

sp_executesql
Building Statements at Run Time

DROP TABLE [Table1]
GO
CREATE TABLE [Table1] (
        [C1] [char] (10)  NULL ,
        [C2] [datetime] NULL )
GO
INSERT Table1 VALUES ('0000000001','1/1/2001')
INSERT Table1 VALUES ('0000000002','1/2/2001')
DROP PROC usp_test1
go

AS





GO
select * from Table1
EXEC usp_test1
EXEC usp_test1 'C1'

Please do not send email directly to this alias.  This is our online
account name for newsgroup participation only.

 
 
 

dynamic sorting in stored procedure

Post by Joe Celk » Fri, 24 Aug 2001 09:51:38


You can "pass a parameter" to an ORDER BY clause with a CASE expression:

 SELECT a, b, c, ...

       THEN a

       THEN b

       THEN CAST(a AS CHAR(10)) || CAST(b AS CHAR(10))

       THEN CAST(b AS CHAR(10)) || CAST(a AS CHAR(10))
       ELSE NULL END AS sort_column
   FROM Foobar
  WHERE ..
  ORDER BY sort_column;

1) Do not use dynamic SQL.  This query can be compiled and will run much faster than constant recompiling .

2) Do not put the CASE expression in the ORDER BY clause.  That is allowed in some proprietary versions of SQL, but it is not standard SQL-92.  Also, it make the engine computer the CASE expression over and over during the sort; if one of the preidicates in a WHEN cluase changes during the sort, things get weird fast.  

3) The CAST() function and concatenation can be used to do multiple column sorts, as shwon above.  Be careful about converting datatypes!

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!