Passing “Order By” parameters w/o utilizing dynamic sql
Thursday, May 14, 2009 8:10Posted in category Tips & Tricks
No Comments
Similar to the SELECT and WHERE clauses, the ORDER BY clause can also utilize CASE statements. Within these CASE statements, the input parameter is evaluated and used to sort the result set as desired.
For example, if you have a table with the following schema:
1: CREATE TABLE [CONTACT_TX_TB] (
2: [CONTACT_SYSID] [int] IDENTITY(1,1) NOT NULL
3: , [TYPE_SYSID] [int] NOT NULL
4: , [TITLE] [varchar](100) NULL
5: , [FIRST_NAME] [varchar](100) NULL
6: , [MIDDLE_NAME] [varchar](100) NULL
7: , [LAST_NAME] [varchar](100) NULL
8: , [CREATED] [datetime] NOT NULL
9: , [CREATED_BY] [int] NOT NULL
10: , [UPDATED] [datetime] NULL
11: , [UPDATED_BY] [int] NULL
12: , [INACTIVE] [bit] NOT NULL
13: )
And the following stored procedure:
1: CREATE PROCEDURE [CONTACT_SELECT_SP] (
2: @CONTACT_SYSID int = NULL
3: , @TYPE_SYSID int = NULL
4: , @TITLE varchar(100) = NULL
5: , @FIRST_NAME varchar(100) = NULL
6: , @MIDDLE_NAME varchar(100) = NULL
7: , @LAST_NAME varchar(100) = NULL
8: , @CREATED_FROM datetime = NULL
9: , @CREATED_TO datetime = NULL
10: , @CREATED_BY int = NULL
11: , @UPDATED_FROM datetime = NULL
12: , @UPDATED_TO datetime = NULL
13: , @UPDATED_BY int = NULL
14: , @INACTIVE bit = NULL
15: )
16: AS
17: BEGIN
18: SET NOCOUNT ON;
19:
20: SELECT TOP 5001
21: [CONTACT_SYSID]
22: , [TYPE_SYSID]
23: , [TITLE]
24: , [FIRST_NAME]
25: , [MIDDLE_NAME]
26: , [LAST_NAME]
27: , [CREATED]
28: , [CREATED_BY]
29: , [UPDATED]
30: , [UPDATED_BY]
31: , [INACTIVE]
32: FROM [CONTACT_TX_TB]
33: WHERE CASE
34: WHEN @CONTACT_SYSID IS NULL THEN 1
35: WHEN [CONTACT_SYSID] = @CONTACT_SYSID THEN 1
36: ELSE 0
37: END = 1
38: AND CASE
39: WHEN @TYPE_SYSID IS NULL THEN 1
40: WHEN [TYPE_SYSID] = @TYPE_SYSID THEN 1
41: ELSE 0
42: END = 1
43: AND CASE
44: WHEN @TITLE IS NULL THEN 1
45: WHEN [TITLE] LIKE @TITLE + '%'THEN 1
46: ELSE 0
47: END = 1
48: AND CASE
49: WHEN @FIRST_NAME IS NULL THEN 1
50: WHEN [FIRST_NAME] LIKE @FIRST_NAME + '%'THEN 1
51: ELSE 0
52: END = 1
53: AND CASE
54: WHEN @MIDDLE_NAME IS NULL THEN 1
55: WHEN [MIDDLE_NAME] LIKE @MIDDLE_NAME + '%'THEN 1
56: ELSE 0
57: END = 1
58: AND CASE
59: WHEN @LAST_NAME IS NULL THEN 1
60: WHEN [LAST_NAME] LIKE @LAST_NAME + '%'THEN 1
61: ELSE 0
62: END = 1
63: AND CASE
64: WHEN @CREATED_FROM IS NULL THEN 1
65: WHEN [CREATED] >= @CREATED_FROM THEN 1
66: ELSE 0
67: END = 1
68: AND CASE
69: WHEN @CREATED_TO IS NULL THEN 1
70: WHEN [CREATED] <= @CREATED_TO THEN 1
71: ELSE 0
72: END = 1
73: AND CASE
74: WHEN @CREATED_BY IS NULL THEN 1
75: WHEN [CREATED_BY] = @CREATED_BY THEN 1
76: ELSE 0
77: END = 1
78: AND CASE
79: WHEN @UPDATED_FROM IS NULL THEN 1
80: WHEN [UPDATED] >= @UPDATED_FROM THEN 1
81: ELSE 0
82: END = 1
83: AND CASE
84: WHEN @UPDATED_TO IS NULL THEN 1
85: WHEN [UPDATED] <= @UPDATED_TO THEN 1
86: ELSE 0
87: END = 1
88: AND CASE
89: WHEN @UPDATED_BY IS NULL THEN 1
90: WHEN [UPDATED_BY] = @UPDATED_BY THEN 1
91: ELSE 0
92: END = 1
93: AND CASE
94: WHEN @INACTIVE IS NULL THEN 1
95: WHEN [INACTIVE] = @INACTIVE THEN 1
96: ELSE 0
97: END = 1
If you add one additional input parameter:
1: , @ORDER_BY varchar(100) = NULL
And the following ORDER BY clause:
1: ORDER BY
2: -- ASCENDING
3: CASE
4: WHEN @ORDER_BY = 'CONTACT_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '1' THEN [CONTACT_SYSID]
5: WHEN @ORDER_BY = 'TYPE_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '2' THEN [TYPE_SYSID]
6: WHEN @ORDER_BY = 'CREATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '8' THEN [CREATED_BY]
7: WHEN @ORDER_BY = 'UPDATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '10' THEN [UPDATED_BY]
8: END
9: , CASE
10: WHEN @ORDER_BY = 'TITLE' OR CONVERT( varchar(100), @ORDER_BY ) = '3' THEN [TITLE]
11: WHEN @ORDER_BY = 'FIRST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '4' THEN [FIRST_NAME]
12: WHEN @ORDER_BY = 'MIDDLE_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '5' THEN [MIDDLE_NAME]
13: WHEN @ORDER_BY = 'LAST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '6' THEN [LAST_NAME]
14: END
15: , CASE
16: WHEN @ORDER_BY = 'CREATED' OR CONVERT( varchar(100), @ORDER_BY ) = '7' THEN [CREATED]
17: WHEN @ORDER_BY = 'UPDATED' OR CONVERT( varchar(100), @ORDER_BY ) = '9' THEN [UPDATED]
18: END
19: , CASE
20: WHEN @ORDER_BY = 'INACTIVE' OR CONVERT( varchar(100), @ORDER_BY ) = '11' THEN [INACTIVE]
21: END
22: -- DESCENDING
23: , CASE
24: WHEN @ORDER_BY = '-CONTACT_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '-1' THEN [CONTACT_SYSID]
25: WHEN @ORDER_BY = '-TYPE_SYSID' OR CONVERT( varchar(100), @ORDER_BY ) = '-2' THEN [TYPE_SYSID]
26: WHEN @ORDER_BY = '-CREATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '-8' THEN [CREATED_BY]
27: WHEN @ORDER_BY = '-UPDATED_BY' OR CONVERT( varchar(100), @ORDER_BY ) = '-10' THEN [UPDATED_BY]
28: END DESC
29: , CASE
30: WHEN @ORDER_BY = '-TITLE' OR CONVERT( varchar(100), @ORDER_BY ) = '3' THEN [TITLE]
31: WHEN @ORDER_BY = '-FIRST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-4' THEN [FIRST_NAME]
32: WHEN @ORDER_BY = '-MIDDLE_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-5' THEN [MIDDLE_NAME]
33: WHEN @ORDER_BY = '-LAST_NAME' OR CONVERT( varchar(100), @ORDER_BY ) = '-6' THEN [LAST_NAME]
34: END DESC
35: , CASE
36: WHEN @ORDER_BY = '-CREATED' OR CONVERT( varchar(100), @ORDER_BY ) = '-7' THEN [CREATED]
37: WHEN @ORDER_BY = '-UPDATED' OR CONVERT( varchar(100), @ORDER_BY ) = '-9' THEN [UPDATED]
38: END DESC
39: , CASE
40: WHEN @ORDER_BY = '-INACTIVE' OR CONVERT( varchar(100), @ORDER_BY ) = '-11' THEN [INACTIVE]
41: END DESC
You will be able to pass in the desired column (either by name or ordinal) as well as the direction (ascending or descending).
*Note: Each unique datatype requires its own CASE statement
You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.