Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tech1
Starting Member
49 Posts |
Posted - 2012-03-21 : 07:48:44
|
Hi.im trying to figure out the most effective way of doing this but im at a brain freeze.Requirement is that I need to return a single record back to the caller based on the following optional fields (but at least one must be filled)AccountNumberAccountNameAccountAddressAccountPostCodeof course, these are my parameters into the SPROChowever, the query should just return the 1 record if found. if there are 0 or more records, then i don't wish to return the entire resultset.The more parameters that are filled in/populated, the more "accurate" the query should be (in otherwords, more AND)how could I go about doing this? |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-21 : 08:17:56
|
Sounds like what you need is what they call a "catch-all" query. Take a look at this blog - she has a description and a nice example there: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
tech1
Starting Member
49 Posts |
Posted - 2012-03-21 : 08:52:16
|
looks great, thanks for that.I hate to use and was trying to avoid the cocatination for the query - any other approaches with good performance? |
|
|
tech1
Starting Member
49 Posts |
Posted - 2012-03-21 : 08:56:44
|
oh another thing, how would i construct the query so that if a few fields are NULL then dont do the AND/WHERE clause? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-21 : 14:38:21
|
You will have to include the logic in the part where you build the WHERE clause of the query, sort of like what she is doing in her example where she does this:IF LEN(@Where) > 0SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3) So you would add something like:IF (@param1 is null AND @param2 is null AND @param3 is nullbegin @Where = '';end |
|
|
|
|
|