Order by based on the parameter passed in SQL server:

Order by based on the parameter passed in SQL server:
Dynamically ordering a result set based on the parameter passed is a powerful technique for ordering your data. The below example shows that and see the issues which we faces.


Initial setup:
create table dept
(
deptid int,
deptname varchar(10)
)

insert into dept values (1,'Sales')
insert into dept values (4,'Production')
insert into dept values (3,'fiance')
insert into dept values (2,'HR')
The following SQL script shows how you might try to use a CASE expression to dynamically order a result set:

DECLARE @OrderByOption int
SET @OrderByOption = 1 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END

deptid deptname
----------- ----------
1 Sales
2 HR
3 fiance
4 Production

(4 row(s) affected)


The script works well if the value of @OrderByOption is set to 1. We change the value as 2 and try.

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Sales' to data type int.
Conceptually, the query offers the ability to order by either the deptid column or the deptname column based on the current value of @OrderByOption. The above statement attempts to order by deptname, but produces the error, " Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'Sales' to data type int "


Why the query works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, we need to recognize that the two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence.

In this example, the CASE statement has two possible values that follow a THEN clause: deptid, which is an integer data type, and deptname, which is a varchar data type. The integer data type has a higher precedence than the varchar data type, so SQL Server attempts to cast the deptname expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.
We can work around this problem by using multiple CASE statements, as the following example shows:

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE WHEN @OrderByOption = 1 THEN deptid END,
CASE WHEN @OrderByOption = 2 THEN deptname END
deptid deptname
----------- ----------
3 fiance
2 HR
4 Production
1 Sales