Best way to write queries for mutiple combinations

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

General SQL Server Forums

New to SQL Server Programming

Best way to write queries for mutiple combinations

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2011-12-11 : 08:33:04

Let's say that I have the following table


  table Book(Id, Title, PublisherId, CategoryId, TypeId, Author, Year, AddedDate)

so if i want to search for all books that have CategoryId = 1 and TypeId = 1 it would be something like:

 
  Select * from Book b where b.CategoryId = 1 and b.TypeId = 1

but the problem is that we have a requirement that: Our application should allow the user to search with any combined book properties
(i. e. with any of the following combination:

with (publisherId)
(CategoryID)
.
.
.
(PublisherId, CategoryId)
(PublisherId, TypeId)
(PublisherId, Author)
.
.
.
(PublisherId, CategoryId, TypeId)
(PublisherId, CategoryId, Author)
.
.
.
(PublisherId, CategoryId, TypeId, Author)
.
.
.
.
(PublisherId, CategoryId, TypeId, Author,.....)

So:

Any better way to write the queries for these combinations than write a query for each one?

Is this a bad idea('but this is a requirement)?? If so how to avoid that??

Is there any common name for such a problem, since in most LOB(Line of business) applications, searchs like this be significant and frequent.

Is it better to handle this in the client application but this will lead to the same problem as SQL is the end point??If so How??

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-11 : 09:42:53

There are a few different ways to do this. The simplest is to have a select clause with something like this:


select
  Id,
  Title
  -- , other columns
from
  theBookTable
where
  (PublisherId = @PublisherId or @PublisherId is null)
  and (CategoryId = @CategoryId or @CategoryId is null)
  -- etc

Here, @PublisherId, @CategoryId etc. are parameters that you would set. If you do not want to use one of the parameters, you would just leave that as null.

Unfortunately, from a performance perspective, this is one of the worst approaches. There is more information and more efficient approaches described here: http://www.sommarskog.se/dyn-search-2008.html

MGA
Starting Member

28 Posts

Posted - 2011-12-11 : 09:49:30

Thanks @sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources