query execution

1)

declare @OrderBy varchar(100) = 'CompanyName'
declare @SortingOrder varchar(5) = 'desc'

Select * from [User] order by (@OrderBy + '  ' + @SortingOrder)

2)

Select * from [User] order by CompanyName desc

If I use the 2nd query then the result comes true (as I expected), but if I use the 1st query then result is not as expected.

I mean that the result set is displayed by ordering the company name in descending order.

What is the wrong in 1st query? S开发者_开发知识库omeone please explain this.

thanks.

For query number 1, you can't specify the column you want to order by dynamically the way you're trying to do it. You could however build a sql string and execute it using EXEC:

declare @OrderBy varchar(100) = 'CompanyName'
declare @SortingOrder varchar(5) = 'desc'
declare @sql varchar(1000)
SET @sql = 'Select * from [User] order by ' + @OrderBy + ' ' + @SortingOrder
EXEC(@sql)

Or if you want to avoid dynamic SQL, you can use a CASE WHEN statement in your ORDER BY:

declare @OrderBy varchar(100) = 'CompanyName'
declare @so varchar(5) = 'desc'

SELECT * FROM [User] 
ORDER BY
  CASE WHEN @OrderBy = 'CompanyName' AND @so = 'desc' THEN CompanyName END DESC,
  CASE WHEN @OrderBy = 'CompanyName' AND @so <> 'desc' THEN CompanyName END