October 31, 2011 at 10:42 am
Hello experts,
in a meeting we were talking about being able to filter the results of a stored procedure in a more flexible way. I suggested that there was probably a way to pass in a definition of how it was being filtered as XML rather than having 10 or so parameters and either dynamic SQL or lots of code like:
( x.category is null or e.category like x.category )
Someone then said it was a good idea and I should look into it. :w00t:
Can anyone recommend any good material as a place to start?
October 31, 2011 at 11:31 am
In SQL 2008, you could probably do name-value pairs, either in XML or (probably better) a Table Value Parameter, and then join to that using some variation of Outer Join.
I'll play around with it now that I'm curious, but I can't imagine it working better than the traditional solutions for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 31, 2011 at 3:26 pm
Question: You say "pass in a definition", but the way I read your post it seems you are wanting to avoid dynamic SQL. If you want to pass in a list of acceptable values to test against, try using a table variable in 2008.
Can you give us a few examples?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 31, 2011 at 4:31 pm
I'm not sure that you will really gain anything using XML or table-valued parameters. Parsing/shredding XML can be expensive and many SQL Server people don't really understand it and XPATH, we should but we don't. Using a table-values parameter would mean some complicated join pattern. While I used to spend a lot of time trying to find solutions better than dynamic SQL for these types of issues, I've come to be more comfortable with dynamic SQL using sp_executesql.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2011 at 8:50 pm
I found Erland Sommarskogs series of articles on dynamic SQL to be both informantive and helpful on this topic.
He has some great worked examples alongside the pro's and con's of various approaches.
November 1, 2011 at 3:56 am
Thanks for the suggestions - it may be I'm ruling out dynamic SQL unnecessarily and I'll look into it. Not sure what is meant by name-value pairs?
As for examples, think of sites such as eBay or Amazon*. Based on your initial search, you are presented with a long list of results and a list of what they think are sensible filters. What I want to do is, given an easily defined initial filter (in my case I am looking at student results data and the first filter is, "what is the current user allowed to see?"), I want to present the user with a list of possible ways to filter the data (generated from the database), and based on their selection, further filter the data and so on. I know I could do this by having a stored procedure which accepts perhaps 10 parameters for the possible ways to filter the data, but if I use that approach, the next time a new political hot potato turns up I have to add an 11th parameter to all relevant procedures to add the relevant functionality. So I'm looking for a way to abstract this - put the effort into a more complex solution now which won't involve re-working every 6 months or so.
*other shopping sites may exist. Any views expressed about the sites or their filtering are the views of the author...etc...
November 1, 2011 at 6:43 am
paul.jones (11/1/2011)
Thanks for the suggestions - it may be I'm ruling out dynamic SQL unnecessarily and I'll look into it. Not sure what is meant by name-value pairs?As for examples, think of sites such as eBay or Amazon*. Based on your initial search, you are presented with a long list of results and a list of what they think are sensible filters. What I want to do is, given an easily defined initial filter (in my case I am looking at student results data and the first filter is, "what is the current user allowed to see?"), I want to present the user with a list of possible ways to filter the data (generated from the database), and based on their selection, further filter the data and so on. I know I could do this by having a stored procedure which accepts perhaps 10 parameters for the possible ways to filter the data, but if I use that approach, the next time a new political hot potato turns up I have to add an 11th parameter to all relevant procedures to add the relevant functionality. So I'm looking for a way to abstract this - put the effort into a more complex solution now which won't involve re-working every 6 months or so.
*other shopping sites may exist. Any views expressed about the sites or their filtering are the views of the author...etc...
Parameterized dynamic SQL is almost always the way to go on that kind of thing.
It's flexible, and it can be written to be very fast, and quite secure.
I've found that refering people to Gail's blog entry on catch-all queries usually helps them understand how to do this well. It's here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 10:36 am
one word of caution on the XML approach: remember that it will tend to encode symbols, etc... which can get a bit hairy when dealing with things like > or < in your criteria, etc...
Since you're using 2008, you might also consider table-valued parameters. Would work the same as your initial concept, just in a native table, rather than an XML message. Depending on where you're using it from, it may actually be easier to do that (we use this kind of concept from C#: fill a list of criteria into a list, and simply pass the list as the table-valued parameter, and it handles it just fine).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply