How could use Dynamic query result in order by?

Abhilash J A

10y1kSQL

Hello everyone,

I have a Dynamic sql query. The query executed order by passing this parameter (@SortColumn, for eg: 'agencyname asc'). And this output insert into the a table and another procedure for select all items from the table. My problem is, 1st query giving order by item but second query not giving order by items. How can I get data from second query as the order by format?

10 Replies

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

Nitin Sontakke10y ago
Accepted answer
One more hint.
The benefit of approach i suggested is, you can even print the variable @dynamicSQL as
print @dynamicSQL;
Then copy and paste the output in another query window and see what is the dynamic sql created by your code. Run it, debug it, etc.
Hope you understand what I am trying to say!
Nitin Sontakke10y ago
First why there is no 'select' word at the beginning of the query.
Second, I really don't understand why you are writing it as follows:
MediaValue,ROW_NUMBER()over(orderby'+@SortColumn+')AS RowNum from MonitorExposureFinal2
If I write it, i will write as follows:
MediaValue from MonitorExposureFinal2 ORDER BY ' + @SortColumn+ '';
Abhilash J A10y ago
Hello Nitin Sontakke,
I have write query like this as you say: error occured

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Kindly replay me.
declare @dynamicSQL nvarchar(max) ='';
declare @SortColumn varchar(100)
set @SortColumn='agencyname asc'
select @dynamicSQL='tempID,ProgramId,ChannelId,convert(date,Date_DL)asDate_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,ROW_NUMBER()over(orderby'+@SortColumn+')ASRowNumfromMonitorExposureFinal2'
execute sp_executeSQL @dynamicSQL
Nitin Sontakke10y ago
I believe that you are better of doing order by at sql server end itself. I am having great difficulty understanding what is it that is not allowing you to do it. Unfortunately, I am not hugely familiar with how Entity Framework works and don't understand how it should matter.
All you have to do is write a stored procedure with dynamic sql select clause and execute it there.
I fail to see the select statement in your GetExposureData stored procedure.
I suggest you assign the dynamic query result to a nvarchar(max) variable and then use it for sp_executeSQL stored procedure.
For example:
declare @dynamicSQL nvarchar(max) = '';
select @dynamicSQL = 'select .....order by ' + @orderByClause
execute sp_executeSQL@dynamicSQL
Abhilash J A10y ago
Hello Nitin Sontakke,
Can I write LINQ for getting ordery by in this situation?
ListmonitorExposure=newList();
var data=db.SP_FinalExposureMonitor_Final().ToList();

/*This is the passing prameter'string colname'.The parameter value will be like this: 'agencyname asc'*/

monitorExposure= from A in data /*What is the LINQ query for order by*/
return monitorExposure;
Nitin Sontakke10y ago
All I can add is there is no predetermined way of retrieving rows from a table which has no primary key defined.
Tables with primary key defined, retrieve rows in order of primary key value, that too because in the absence of specific order by clause, sql server finds it quicker to return rows in the same order as they are found in the index.
The standard sql states that whenever results are expected to be in order of a particular column, order by clause must be specified in select statement.
Giving order by clause while inserting rows in a table, does not guarantee that rows are inserted in that order or will be retrieved in that order in the absence of explicit order by clause.
Abhilash J A10y ago
Hello Midhun,
Thanks for replay me. 1st procedure inserting data in to table is working fine. Also 1st procudure giving order by way.But when inserting data in to table, it is not order by way.
In 2nd procedure just selectings item from the table. Yes, I have tried order by way in 2nd query through passing param in it @SortColumn='agencyname asc'. But below procedure name can not getting while updating .EDMX file in MVC4. Why this happening? or Is another way to get order by passing param like this@SortColumn='agencyname asc'
createprocedureGeExposureData
@SortColumnvarchar(100)
AS
BEGIN
EXEC('tempID,ProgramId,ChannelId,convert(date,Date_DL)asDate_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,ROW_NUMBER()over(orderby'+@SortColumn+')ASRowNumfromMonitorExposureFinal2'
)
END
Midhun Tp10y ago
Hi,

In your first procedure you are selecting rownumber in sql1. But while inserting to table, there is no column corresponding to row number in that table. Is that working fine? Please check whether there is a column mis match in insert query.

Also in your second procedure you are just using select query, so why don't you use order by clause there.
Abhilash J A10y ago
1st Procedure:
declare@sql1NVARCHAR(max)
declare@TransIdvarchar(100)
declare@SortColumnvarchar(100)

SET@TransId=1548722
SET@SortColumn='agencynameasc'

BEGIN
set@sql1='selectROW_NUMBER()over(orderby'+@SortColumn+')ASRowNum,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,MediaValuefrom
dbo.MonitorExposureFinalWHERETrans_Id='+@TransId+'';
BEGIN
deletefromdbo.MonitorExposureFinal2
insertintoMonitorExposureFinal2(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
)
exec(@sql1)
END
END
2nd Procedure:
selecttempID,ProgramId,ChannelId,convert(date,Date_DL)asDate_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,MediaValuefromMonitorExposureFinal2

In the 1st procedure there is happening Order by but not inserting items in the table order by way. How can I select orderby items?
Midhun Tp10y ago
Hi,

Can you please post your queries. Actually why are you passing result to an table and then retrieving the same?