Get the order by clause on the bases of value passed

Puneet20884
Posted by Puneet20884 under Sql Server category on | Points: 40 | Views : 675
Hi,

Sometimes we need to pass the variable to our SQL Block in Stored Proc or User Defined Function.
e.g. in code section of dotnetfunda

either
Title Ascending or Title Decending
or
Views Ascending or Views Decending
or
any other fields.

So the following code is handy in such cases.

The following coode is self-explanatory but still any doubt, please let me know.

declare @sortorder1 varchar(200)
set @sortorder1 = 'CustID DESC'

declare @sortorder2 varchar(200)
set @sortorder2 = 'CntID DESC'

SELECT
*
FROM
YourTableName FD
ORDER BY
CASE WHEN @sortorder1 = 'CustID ASC' THEN CustomerID END DESC,
CASE WHEN @sortorder1 = 'CustID DESC' THEN CustomerID END DESC,
CASE WHEN @sortorder2 = 'CntID ASC' THEN CountryID END ASC,
CASE WHEN @sortorder2 = 'CntID DESC' THEN CountryID END DESC


Note: One drawback of this code is that we have only fixed orderby clauses, in case a new orderby clause is required to be added;
we need to modify the above code for that value.

Thanks
Puneet

Comments or Responses

Login to post response