Dynamic order by clause without a case when statement

Question

Post reply

Dynamic order by clause without a case when statement

Freefall99

Mr or Mrs. 500

Points: 533
More actions
October 9, 2014 at 1:01 pm

#294105

Hello I am trying to make a dynamic order by clause for a procedure that does not involve a case when statement and also does not involve dynamic sql. I have the parameters for the select statement being given to a procedure from a web interface. I have also wrote a sql function that can take the many fields which need to be in the order by clause directly from the web interface and can check if they are actually fields in the table, than constructs the "Order By Name, age, etc".
I would like something along the lines of
Declare @variables nvarchar(50)
Declare @orderbyField nvarchar(50)
set @variables = 'VarColumn1'
set @orderbyField = 'Age_Asc;Height_Desc;' --I know that this wont work the way it is but the function will take care of that
Select * from dbo.table
where variables = @variables
Order by dbo.Function_Returns_orderField(@orderfield) --function returns nvarchar Age Asc, Height Desc
-A case when statement wont work because we have many fields
-Dynamic sql may be dangerous coming from a web interface. I could always construct the entire string and exec() it but I would love to hear your opinions on the matter. Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Your best option would be to sort the results in the front end.

Using a scalar function is a great performance problem.

Using Dynamic code can be safe if used correctly.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Freefall99 Mr or Mrs. 500 Points: 533 More actions · Answer 2

Luis Cazares (10/9/2014)
Your best option would be to sort the results in the front end.
Using a scalar function is a great performance problem.
Using Dynamic code can be safe if used correctly.

The problem is that it's so much data coming to the front end a lot of computers would not be able to handle it, I had to make the procedure select with row numbers and only return certain row numbers which are also a parameter for the procedure. I will read into dynamic code in the morning.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

At some point you have to trust the application not to send you malicious data, once again that doesn't mean that end users of the application should be sending commands directly to SQL but at some point you have to tell whoever is designing the app that they have to sanitize their input before sending it to your DB.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Freefall99 (10/9/2014)
Luis Cazares (10/9/2014)
Your best option would be to sort the results in the front end.
Using a scalar function is a great performance problem.
Using Dynamic code can be safe if used correctly.
The problem is that it's so much data coming to the front end a lot of computers would not be able to handle it, I had to make the procedure select with row numbers and only return certain row numbers which are also a parameter for the procedure. I will read into dynamic code in the morning.

Then you might have a design problem. There's no reason to return that volume of information as no one will be able to consume it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You need to read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

It sounds like this exactly the kind of query you are doing here and performance can really stink if you don't do these things correctly. You need to get rid of that scalar function as your where predicate and embrace dynamic sql for this process.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Freefall99 Mr or Mrs. 500 Points: 533 More actions · Answer 6

Link is giving me an internal server error. Is that the correct one? Thank you.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Freefall99 (10/10/2014)
Link is giving me an internal server error. Is that the correct one? Thank you.

Oh crap. Gail's site is down. That is the correct link. Let me see if I can find an archive of it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Here is the google cached version. This should work for you.

http://webcache.googleusercontent.com/search?q=cache:FnRGE_r0_EIJ:sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/+&cd=1&hl=en&ct=clnk&gl=us

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

LOL. The original link seems to be working again now anyway. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Freefall99 Mr or Mrs. 500 Points: 533 More actions · Answer 10

Freefall99

Mr or Mrs. 500

Points: 533

October 10, 2014 at 8:20 am

#1751661

Thanks Sean