Help with creating a SQL query with optional multi-value parameters
June 26, 2007 12:38 PM Subscribe
Looking for some guidance in building an "advanced search" query using XML and optional multi-value parameters.
I'm building a web search form to allow users to look up people based on various parameters- name, location, title, etc. These parameters are all optional and some are multi-valued. Unless someone comes up with a good reason otherwise, it seems logical to me to pass these parameters to a stored proc as an XML doc (I'm using MS SQL 2005). I DO NOT want to build a SQL statement dynamically if I can avoid it.
So, I'd wind up with parameters like so:
Am I missing something obvious? Is there a better way to tackle this?
I'm building a web search form to allow users to look up people based on various parameters- name, location, title, etc. These parameters are all optional and some are multi-valued. Unless someone comes up with a good reason otherwise, it seems logical to me to pass these parameters to a stored proc as an XML doc (I'm using MS SQL 2005). I DO NOT want to build a SQL statement dynamically if I can avoid it.
So, I'd wind up with parameters like so:
<SearchParams> <Name>MKUltra</Name> </SearchParams> or <SearchParams> <State>NY</State> <State>NJ</State> <State>CT</State> </SearchParams> or <SearchParams> <Name>MKUltra</Name> <State>NY</State> <State>NJ</State> <State>CT</State> <Title>Stud Muffin</Title> </SearchParams>My gut says the best approach is to dump them into a temp table and do some shenanigans to restructure the data into a two-column table with "Name" and "Value" as the columns so I can JOIN on it, but I'm not sure. My main concern is joining on this table when there's no relevant parameter passes (e.g. "State", in the first example).
Am I missing something obvious? Is there a better way to tackle this?
Ok this is my way, but it's not the only way.
I pass multi-value parameters to an SQL query as a comma-delimited string. Then I use a SQL function that take the comma-delimited string and returns a table variable.
This is elegant because you can just "join" straight to the result of the function in one line. There is an excellent example (including the code for the conversion function) at this link.
Your search would ends up looking something like this:
posted by pivotal at 1:44 PM on June 26, 2007
I pass multi-value parameters to an SQL query as a comma-delimited string. Then I use a SQL function that take the comma-delimited string and returns a table variable.
This is elegant because you can just "join" straight to the result of the function in one line. There is an excellent example (including the code for the conversion function) at this link.
Your search would ends up looking something like this:
SELECT Users.*
FROM
[dbo].[Users]
INNER JOIN
[dbo].[myStringToTableFunc] ( "NY,NJ,CT" ) State
ON Users.[StateD] = State.[String]
WHERE
Users.[Title] = "Stud Muffin"
posted by pivotal at 1:44 PM on June 26, 2007
Dammit just re-read your question and saw the bit about the "optional" State parameter. This might get ugly.
I would also go straight towards a dynamic SQL approach, with a test for a null parameter, but this is not nice from an optimisation point of view.
Another option is to set default values for optional parameters in a stored proc. So you would have something like:
Then you can use "Like" clauses to match on Name and Title, and the "%%" will match ANYTHING if no value is passed. The full states list will return every result regardless of state if you pass nothing.
To reverse the logic (e.g. return nothing if no param is passed), use an empty string as the defaults.
posted by pivotal at 1:52 PM on June 26, 2007
I would also go straight towards a dynamic SQL approach, with a test for a null parameter, but this is not nice from an optimisation point of view.
Another option is to set default values for optional parameters in a stored proc. So you would have something like:
CREATE PROCEDURE myProc (
@Name varchar(255) = "%%",
@States varchar(1000) = "AK,CA,NY...list of ALL states",
@Title varchar(255) = "%%" )
Then you can use "Like" clauses to match on Name and Title, and the "%%" will match ANYTHING if no value is passed. The full states list will return every result regardless of state if you pass nothing.
To reverse the logic (e.g. return nothing if no param is passed), use an empty string as the defaults.
posted by pivotal at 1:52 PM on June 26, 2007
Response by poster: Better = simpler. I'm not sure how a non-XML-based solution could achieve more flexibility, but I'm open to seeing how.
pivotal- I don't want to use CSV because it requires extra data manipulation before sending off and it doesn't get around the optional issue- INNER JOIN [dbo].[myStringToTableFunc] ( "" ) will always return 0 rows. I do use that technique when I'm only dealing with one parameter, but it's not flexible IMO because you need to define each parameter separately in the SP.
posted by mkultra at 1:53 PM on June 26, 2007
pivotal- I don't want to use CSV because it requires extra data manipulation before sending off and it doesn't get around the optional issue- INNER JOIN [dbo].[myStringToTableFunc] ( "" ) will always return 0 rows. I do use that technique when I'm only dealing with one parameter, but it's not flexible IMO because you need to define each parameter separately in the SP.
posted by mkultra at 1:53 PM on June 26, 2007
Response by poster: To clarify- my issue isn't with single-entry parameters (name, title, etc.), because there will ALWAYS be one value passed, even if it's just an empty string (yes, I'm using LIKE for that)- it's with parameters which can occur 0+ times, the way HTML checkbox form elements are passed.
posted by mkultra at 1:56 PM on June 26, 2007
posted by mkultra at 1:56 PM on June 26, 2007
Gah yeah sorry I'll just crawl back under my rock. I totally misinterpreted your question.
This page has a little ditty on how to extract xml values as a table (about half way down), which gets you part way thre, but you're back to a dynamic query if the table is empty.
posted by pivotal at 2:04 PM on June 26, 2007
This page has a little ditty on how to extract xml values as a table (about half way down), which gets you part way thre, but you're back to a dynamic query if the table is empty.
posted by pivotal at 2:04 PM on June 26, 2007
« Older Can you help me build the perfect piece of... | I don't want to start my own needle exchange Newer »
This thread is closed to new comments.
posted by scottreynen at 1:24 PM on June 26, 2007 [1 favorite]