sort order

Question

sort order

Mike Levan

SSChampion

Points: 14201
More actions
June 21, 2007 at 7:49 am

#153644

select empid,empname,jobrole from emp order by empid

I have a store proc with the above query. How can I do a sort order dynamically from app, say for example if a user wants the sort order according to jobrole instead of empid. How shud i update my proc to do a sort order dynamic.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

change the sp to take an input for sort order. have your query written as dynamic query and add the sort order what you get as input parameter to the dynamic query. remember that you will have few performance issues with this.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

You can also try this

DECLARE @SortBy varchar(2)

SET @SortBy = 'ID'

select empid,empname,jobrole

FROM Emp

ORDER BY

CASE WHEN @SortBy = 'ID' THEN empid

ELSE jobrole

END

provided EmpID and JobRole are the same datatype.

David McFarland SSChampion Points: 11815 More actions · Answer 3

If you wish to avoid dynamic SQL, and if the columns that you want them to potentially sort on are fixed, then you can also do it with a CASE in the ORDER BY.

Edit: I see SQL Junkie beat me to it.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

To complete this, I strongly suggest you validate the input order variable :

if @SortOrder IN ('jobid', 'empName', 'empid')

begin

end

else

begin

raiserror ('Invalid sort order', 13, 1) --this avoids any possible sql injection

end

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 5

You could turn your Stored Procedure into a Function and then let the calling routine sort anyway it wants, for example:

if object_id('ufnEmployees') is not null drop function ufnEmployees

go

create function ufnEmployees () returns table

as

return (select * from northwind..employees)

go

select *

from dbo.ufnEmployees()

order by lastname

--James.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Still need to have a dynamic sort... or 1 query / sp / function per variation to have the optimal query plan...

Mike Levan SSChampion Points: 14201 More actions · Answer 7

I have a problem when doing dynamic sort order, it is not allowing dynamic sort order when am using DISTINCT for select statement.

How can i get both in one proc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Should be able to... can you post the code?

If worse comes to worse, you can always do the distinct query in a derived table, then apply the dynamic sort.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Once again, your ideas are extemely sound, but I see no code from you...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fakhri M SSC Veteran Points: 262 More actions · Answer 10

you can put your query in a varchar variable like this:

declare @STR varchar(4000)

set @STR='select * from tbl order by ' + @orderByPhrase

and exexute @STR:

exec (@str)

Vladan SSC-Insane Points: 22072 More actions · Answer 11

But you didn't mention DISTINCT in your original question... does it mean that the sp is actually different than what you posted?

Helping you would be easier if you post the procedure as it is. So far I don't see any reason why you should use a procedure at all. A view should do the trick, and you can select from a view easily, specifying ORDER BY in your application as needed. No need to create dynamic SQL for that... but then, maybe your sp is a lot more complicated and can't be replaced with a view - problem is, we can hardly help if we don't know it.

Mike Beckner SSChasing Mays Points: 604 More actions · Answer 12

SQL Junkie,

I had this same problem and found your solution.

Your code worked perfectly. I wish I knew why the Sort Order in SSRS 2000 isn't working, though. IN any case, it's quicker to code it in a SP than to do it in the report properties.

THANKS!

Does anybody happen to know if the on-board sorting in 2005 works any better?

Mike

Sergiy SSC Guru Points: 110208 More actions · Answer 13

select empid,empname,jobrole

from dbo.emp

order by ISNULL(@DisableEmpidOrder, empid), ISNULL(@DisableEmpnameOrder, empname), ISNULL(@DisablejobroleOrder, jobrole)

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 14

From App you wrote?

If you use the ADO library, just put the sorting string to the Sort method.

N 56°04'39.16"
E 12°55'05.25"