SQL Server Performance Forum – Threads Archive
Search StoreProcedure Syntax
I’m rewriting our company’s app and I’m trying to figure out the most efficient way to search. For example I have a search box with First, Last, City, and state. The user can enter any combination of these fields to search on. I pass all four to my stored procedure. Right now I build a sql select string based on what values are not null (ie. the user searches only on last name) and then do exec(<sql string><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />. Is there a better way to do this? Anyone care to post a select stored procedure they use when a few of the params will be optional?
One of the following will do. Will need to make sure at least one of the search criteria is not null. 1. WHERE (First = @First OR @First IS NULL)
AND (Last = @Last OR @Last IS NULL)
… 2. Select @First = isnull(@First,’%’), @Last = isnull(@Last, ‘%’), …
…
WHERE First LIKE @First
AND Last LIKE @Last
…
Tim, your approach is more efficient, the only change I would suggest is to use exec executeSQL with parameters, this way you’ll have your execution plan cached and reused instead of constant query compilation. The only more efficient way is to call one of many procs depending on parameter passed. However, it is maintenance nightmare unless you have tool that generates such code (both procs and their ado calls) based on template. My former company developed and used such tool.
However, if performance is not critical suggestion above gives you the simplest solution I know about.
See if this helps:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9143
http://www.sommarskog.se/dyn-search.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>