Last post Nov 21, 2016 04:41 PM by madjester
Nov 19, 2016 09:27 AM|madjester|LINK
Is there any way to dynamically create the Order By Clause? For example let's say I have a web app that displays a list of search words. Each search word corresponds to a name of a database table column.
The user places a check in the check box next to each word that he wants to search. Each word has an Order Of Importance number associated with it and initially, each is set to zero.
The user can set the value of the Order of Importance number, and the word with the highest Order of Importance number is the most important.
Please see my example below.
CheckBox Search Words Order Of Importance[ ] Word1 0
[x] Word2 4
[x] Word3 2
[ ] Word4 0
[x] Word5 1[x] Word6 3
Then the search words and their associated order of importance numbers are passed in as arguments of a stored procedure and then sent to a database.
The order of importance number of zero next to a word means that the user did not place a check mark on that word so it is not sent to the database.
The order of importance numbers will be used to create the Order By clause.
Nov 21, 2016 02:16 AM|Chris Zhao|LINK
You could dynamically specify the sort order in your application code.
create procedure uspCallAndSort
@sql varchar(2048), --exec dbo.uspSomeProcedure arg1,'arg2',etc.
@sortClause varchar(512) --comma-delimited field list
insert into #tmp EXEC(@sql)
declare @msql varchar(3000)
set @msql = 'select * from #tmp order by ' + @sortClause
drop table #tmp
Nov 21, 2016 06:11 AM|Prince Srivastava|LINK
use Case Statement like below.
DECLARE @orderBy varchar(10)= 'Name'
SELECT * FROM Member
ORDER BY CASE WHEN @orderBy='Name' THEN SurName
Nov 21, 2016 04:41 PM|madjester|LINK
Hi Chris, thanks for your reply. I have come up with a solution where I passed in the order of importance numbers for and the search words. Then using a loop to sort the search words. After seeing your solution I think your method is more efficient since
the sort is done in the app and not in the database.