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')
If we will execute
following sql query:
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.
Cause:
When we use variable in the ORDER BY clause then it must be a column name. It
cannot be column position.
Solution:
Use the
column name instead of column position. For example:
DECLARE @Col_Name VARCHAR(128) = 'ID'
SELECT *
FROM
tblSource
ORDER BY CASE
WHEN @Col_Name = 'ID' THEN CAST(ID AS SQL_VARIANT)
WHEN @Col_Name = 'vcData' THEN CAST(vcData AS SQL_VARIANT) END ASC
Output:
ID
|
vcData
|
1
|
PC
|
2
|
Laptop
|
3
|
Mobile
|
6 comments:
Although this doesn't error, it also doesn't work. In this case the order by clause is simply ignored. You'll notice that the output is not sorted by ID descending. It's equivalent to the following...
SELECT *
FROM tblSource
Yes, you are correct. Now I have changed the script. It will work.
thanks a lot. It is working great.
Thanks for this tip. Very useful!
hnmmmmmmmmmmm
try for dynamic query it is better
Post a Comment