CODEWITHSUNDEEP
sqlsql-server
Ahmed Abd el-hameed
Ahmed Abd el-hameed

Reputation: 51

how to use string value in Order By clause?

i have a table in the database that consists of 10 columns, the header name for each column is

'Col' + column number

look like this

id|Col1|Col2|Col3|Col4|Col5|Col6

i have a SQL statment that run through a Stored procedure this stored take column number as a parameter

@ColumnNumber nvarchar(10)

all i want to do is ordering the result by column number that will be passed in the parameter , something like this

Select   * from [Table] order by  ('Col' + @ColumnNumber)

but it doesn't work with me

Upvotes: 1

Views: 166

Answers (4)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

You need to create dynamic SQL to make it work like

DECLARE @ColumnNumber nvarchar(10)
DECLARE @strSQL nvarchar(max)
SET @ColumnNumber = '1'

SET @strSQL = 'Select * from [Table] order by Col' + @ColumnNumber
EXEC(@strSQL)

UPDATE : If you have @List variable of XML type which have multiple ids then you can get in comma separated format like below

DECLARE @RecordIds nvarchar(max)
DECLARE @List XML = '<Records><id>1</id></Records><Records><id>2</id></Records>'

SELECT  @RecordIds = STUFF((SELECT ',' + RecordId.value('.','varchar(5)') 
                            FROM @List.nodes('Records/id') AS Test(RecordId)
                FOR XML PATH (''))
                , 1, 1, '')

Then you can use that comma separated string in your query like 

SET @strSQL = 'Select * from [Table] where Id in ( ' + @RecordIds + ' ) order by Col' + @ColumnNumber

Upvotes: 3

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

Try this -

DECLARE @ColumnNumber NVARCHAR(10)
DECLARE @sql NVARCHAR(100)

SET @ColumnNumber = '1'
SET @sql = 'Select * from TEST_EXISTS order by COL' + @ColumnNumber + ' DESC'

PRINT @sql

EXEC (@SQL)

Upvotes: 0

valex
valex

Reputation: 24144

Without using dynamic SQL you can do it using CASE statement:

SELECT * FROM [Table]
        ORDER BY CASE @ColumnNumber
           WHEN 1 THEN Col1
           WHEN 2 THEN Col2
           WHEN 3 THEN Col3
           .....
           WHEN 10 THEN Col10
         END;

Upvotes: 3

Devart
Devart

Reputation: 121922

Try this one -

DECLARE @ColumnNumber INT
SET @ColumnNumber = 1

SELECT *
FROM [Table]
ORDER BY 
    CASE @ColumnNumber
        WHEN 1 THEN Col1
        WHEN 2 THEN Col2
        WHEN 3 THEN Col3
    END

Upvotes: 3

Related Questions