Mar 18, 2014

How to use variable in ORDER BY clause in sql server


Let us assume we have created a table and inserted some data into using following sql query:

CREATE TABLE tblSource(
    ID INT IDENTITY PRIMARY KEY,
    vcData VARCHAR(500)
)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

Variable in ORDER BY clause in sql server

DECLARE @Col_Name VARCHAR(128) = 'ID'

SELECT *
FROM tblSource 
ORDER BY CONVERT(SYSNAME,@Col_Name) DESC

Output:

ID
vcData
1
PC
2
Laptop
3
Mobile

Note: There is limitation of using varaible in ORDER BY clause. We cannot use column position in order by cluase. For example:

DECLARE @Position INT = 1
SELECT * FROM tblSource  ORDER BY @Position  DESC
  
We will get error message :

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

What is Adhoc query in sql server: Cached Query Plan
Subquery vs inner join which one is better in sql server
Difference between actual execution plan and estimated execution plan in sql server
Best ways to pass comma delimited varchar variables to IN clause sql server

1 comment:

  1. Anonymous7/09/2015

    While this method avoids the error message, in my testing it doesn't work

    ReplyDelete