January 24, 2008 at 10:54 am
Hi,
I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.
Below is my procedure..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),
as
BEGIN
SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END
The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.
I am not able to figure out why.
Any help in this will be appriciated.
Regards,
Salim.
January 24, 2008 at 11:47 am
The problem is that by using a variable that way, the result is the same as if your query were:
[font="Courier New"]SELECT e.C1, e.C2, e.C3, e.C4, ROW_NUMBER() OVER(ORDER BY 'C1') as RowNum
FROM T1 e[/font]
The constant value 'C1' is the same for every row, so no additional sorting can be done.
Your problem can be solved with a CASE expression:
[font="Courier New"]SELECT e.C1, e.C2, e.C3, e.C4,
ROW_NUMBER() OVER(ORDER BY CASE @sortBy
WHEN 'C1' THEN C1
WHEN 'C2' THEN C2
WHEN 'C3' THEN C3
ELSE C4
END) as RowNum
FROM T1 e[/font]
If C1, C2, C3, and C4 have different data types (date vs character vs numeric), then you will need to covert them all to a common type, usually a string type. For example, if C1 is varchar(100), C2 is datetime, C3 is int, and C4 is varchar(50), then the CASE clause would look like:
ORDER BY CASE @sortBy
WHEN 'C1' THEN C1
WHEN 'C2' THEN convert(varchar(100), C2, 120)
WHEN 'C3' THEN convert(varchar(100), C3)
ELSE C4
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply