SP with variable number filled of parameters

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

SP with variable number filled of parameters

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-06-14 : 16:15:48

I have a SP search_post (e.g)

@id int,
@author varchar(40),
@keyword varchar(40),

select * from
posts
where
id = @id and author =@author and message like @keyword

in my case a user can choose to search by one or more of this elements.

what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled

one approach I have thought out is this

if (@id IS NOT null)
select * from
posts where id = @id

if (@author IS NOT null)
select * from
posts where author= @author

if (@keyword IS NOT null)
select * from
posts where keyword = (@keyword

but this does not really take care of the posibility that all of them or some of them will be null while others will not

any suggestions ?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-14 : 16:29:43

here's a good essay on dynamic search conditions, which is what you have:

http://www.sommarskog.se/dyn-search.html

elsasoft.org

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-06-14 : 16:48:03

great thank you it has some very good information

Kristen
Test

22859 Posts

Posted - 2007-06-15 : 03:12:37

If your tables are not too huge you can probably just use:


select * from
posts
where      (@id IS NULL OR id = @id)
       AND (@author IS NULL OR author = @author)
       AND (@keyword IS NULL OR message like @keyword)

and pass in, or Default, parameters as NULL for anything that the user does not provide criteria for.

If the performance is poor then adopt the Dynamic Search SQL route that jezemine has pointed you to. (We use the above approach on tables with millions of rows, so it will probably work and is a lot less effort to implement, maintain and Debug! than building dynamic SQL

Kristen

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-06-15 : 09:42:36

thank you for the information Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-15 : 10:10:54

that technique is in Erland's article in the "static sql" chapter, here: http://www.sommarskog.se/dyn-search.html#OR_ISNULL

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources