And, to my chagrin, there is a typo. The first two case statements should be testing "when @parm1 = 3" not "when @parm2 = 3".
I didn't see it in time to edit it. So it goes.
And, to my chagrin, there is a typo. The first two case statements should be testing "when @parm1 = 3" not "when @parm2 = 3".
I didn't see it in time to edit it. So it goes.
Guys, lpope has it right. You don't need dynamic SQL, just ORDER BY CASE expressions that test your parameters. Look at the code below. Looks clunky but runs sweet. Lets you sort by up to three columns, and could easily be extended to six or twelve. Handles both ascending and descending sorts by a simple intuitive convention. Took 5 minutes to code, because after you get the first set done, everything else is cut, paste, and replace. One execution plan, no SQL injection risk. ;-)
Painful to change? Come on.... what could be simpler? All you're doing is adding a few more WHEN lines. Its a small price to pay for the performance and the security.
-- values for parameters: 1 = col [A], 2 = col[B], 3 = col[C]
-- negative values mean descending order
declare @parm1 int, @parm2 int, @parm3 int
-- sort on column B, column C descending, and finally column A (play around with these settings.)
select @parm1 = 2, @parm2 = -3, @parm3 = 1
;with sample (A,B,C) as
(select 1,2,3 union all
select 9,2,5 union all
select 3,4,5 union all
select 2,3,1 union all
select 2,2,1
)
select A,B,C from sample
order by
case when @parm1 = 1 then A
when @parm1 = 2 then B
when @parm2 = 3 then C
else null end ASC,
case when @parm1 = -1 then A
when @parm1 = -2 then B
when @parm2 = -3 then C
else null end DESC,
case when @parm2 = 1 then A
when @parm2 = 2 then B
when @parm2 = 3 then C
else null end ASC,
case when @parm2 = -1 then A
when @parm2 = -2 then B
when @parm2 = -3 then C
else null end DESC,
case when @parm3 = 1 then A
when @parm3 = 2 then B
when @parm3 = 3 then C
else null end ASC,
case when @parm3 = -1 then A
when @parm3 = -2 then B
when @parm3 = -3 then C
else null end DESC