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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 blind selection type query

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)

AccountNumber
AccountName
AccountAddress
AccountPostCode

of course, these are my parameters into the SPROC

however, 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/
Go to Top of Page

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?
Go to Top of Page

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?
Go to Top of Page

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) > 0
SET @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 null
begin
@Where = '';
end
Go to Top of Page
   

- Advertisement -