How can I use order by in dynamic query?

Hello everyone,

From this below query, Order by can not working when I am passing parameter @SortColumn. But it working directly passing value. For eg, if I am giving AgencyName asc instead of @SortColumn. Its working fine. How to solve this issue when I am passing parameter?

DECLARE @StartRowNum varchar(10)
DECLARE @EndRowNum varchar(10)
DECLARE @SortColumn varchar(100)
DECLARE @TransId varchar(100)
SET @SortColumn = 'AgencyName asc'
SET @TransId = 1481143433
SET @EndRowNum = 50
SET @StartRowNum = 0
begin
select ROW_NUMBER() over (order by @SortColumn ) AS RowNum, tempID,ProgramId,ChannelId,Date_DL,StartTime,EndTime,Viewers,
Tx,TVR,CVR,DVR,
Details_Id,
MastersId,BrandsId,ItemsId,Note,PaidForOrProp,Verbal,Indexes,Seconds,
GradesId,ChannelName,CountryName,WebSiteName,ProductionName,BrandName,ItemName,
Product_company_id,CompanyName,ProductGroupsId,
SectorsId,SubSectoresId,AgenciesId,GroupName,SectorName,SubsectoreName,
AgencyName,agencyID,QualityGrade,CPH_DL,CPH_UN,Country_CPH,Country_UN,
Web_CPH,Web_UN,
AdFactor,Duration_Factor,DaypartName,DaypartId,DaypartFactor,CinemaFactor,DigitalFactor,MediaValue from
dbo.MonitorExposureFinal WHERE Trans_Id = @TransId
end

Hi,

Please try like below -

EXEC('select ROW_NUMBER() over (order by '+@SortColumn+' ) AS RowNum, tempID,ProgramId,ChannelId,Date_DL,StartTime,EndTime,Viewers,

Tx,TVR,CVR,DVR,

Details_Id,

MastersId,BrandsId,ItemsId,Note,PaidForOrProp,Verbal,Indexes,Seconds,

GradesId,ChannelName,CountryName,WebSiteName,ProductionName,BrandName,ItemName,

Product_company_id,CompanyName,ProductGroupsId,

SectorsId,SubSectoresId,AgenciesId,GroupName,SectorName,SubsectoreName,

AgencyName,agencyID,QualityGrade,CPH_DL,CPH_UN,Country_CPH,Country_UN,

Web_CPH,Web_UN,

AdFactor,Duration_Factor,DaypartName,DaypartId,DaypartFactor,CinemaFactor,DigitalFactor,MediaValue from

dbo.MonitorExposureFinal WHERE Trans_Id = '+@TransId)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp10y ago
Accepted answer
Hi,
Please try like below -
EXEC('select ROW_NUMBER() over (order by '+@SortColumn+' ) AS RowNum, tempID,ProgramId,ChannelId,Date_DL,StartTime,EndTime,Viewers,
Tx,TVR,CVR,DVR,
Details_Id,
MastersId,BrandsId,ItemsId,Note,PaidForOrProp,Verbal,Indexes,Seconds,
GradesId,ChannelName,CountryName,WebSiteName,ProductionName,BrandName,ItemName,
Product_company_id,CompanyName,ProductGroupsId,
SectorsId,SubSectoresId,AgenciesId,GroupName,SectorName,SubsectoreName,
AgencyName,agencyID,QualityGrade,CPH_DL,CPH_UN,Country_CPH,Country_UN,
Web_CPH,Web_UN,
AdFactor,Duration_Factor,DaypartName,DaypartId,DaypartFactor,CinemaFactor,DigitalFactor,MediaValue from
dbo.MonitorExposureFinal WHERE Trans_Id = '+@TransId)
+2