I am passing sortExp to the followin SP but it does not give me sorted output but if i hardcode the columnname it works fine.Why is the reason? Is my approach right? here is my SP
declare @sortExp nvarchar(100) set @sortExp = 'FirstName'
select * from (select ROW_NUMBER() over (order by @sortExp ) as RowNumber,* from Users) myQueryAlias where RowNumber between 1 and 5
Only way is using Dynamic SQL
declare @sortExp nvarchar(100) , @sql varchar(8000)
set @sortExp = 'FirstName'
set @sql='select * from (select ROW_NUMBER() over (order by '+@sortExp+' ) as RowNumber,
* from Users) myQueryAlias where RowNumber between 1 and 5'
exec(@sql)
or
If the table columns dont change
declare @sortExp nvarchar(100)
set @sortExp = 'FirstName'
select * from (select ROW_NUMBER() over
(
order by
case @sortExp
when 'FirstName' then Firstname
when 'LastName' then Lastname
end
) as RowNumber,
* from Users) myQueryAlias where RowNumber between 1 and 5
I think using CASE in the way Madivanan suggests:
case @sortExp
when 'FirstName' then Firstname
when 'LastName' then Lastname
end
only works if the data types / max length of the columns are the same, otherwise you need multiple CASE statements:
case WHEN @sortExp = 'FirstName' THEN Firstname ELSE NULL END, case WHEN @sortExp = 'LastName' THEN Lastname ELSE NULL END end
