input parameter as theQuote:>-----Original Message-----
>I need to write a stored procedure that will take an
clause, so I can't justQuote:>sort order (ORDER BY clause). SQL Server demands that the
ORDER BY clause
>refer directly to one of the columns in the SELECT
Quote:>put the variable into the order by clause. Any
>Thanks . . .
Sr. Data Architect / DBA
PlanetJam Media Group
Quote:> I need to write a stored procedure that will take an input parameter as
> 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 . . .
Building Statements at Run Time
DROP TABLE [Table1]
CREATE TABLE [Table1] (
[C1] [char] (10) NULL ,
[C2] [datetime] NULL )
INSERT Table1 VALUES ('0000000001','1/1/2001')
INSERT Table1 VALUES ('0000000002','1/2/2001')
DROP PROC usp_test1
select * from Table1
EXEC usp_test1 'C1'
Please do not send email directly to this alias. This is our online
account name for newsgroup participation only.
SELECT a, b, c, ...
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
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!
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!
Is there anyway I can dynamically sort using ORDER BY within a stored