The best way to use a stored procedure for a search? 1

[modika11]

Hi All,

although i have done this using several ways i am sitting here thinking what way is actually the best? I have a job search which will search against mutiple criteria. I have written stored procedures whith loads of conditional statments depending on what search criteria has been provided depends on how the SQL is written, this is not dynamic SQL it is normal SQL with a very detailed (complicated) where clause. I have also done it using dynamic SQL within a stored procedure, obviously with some conditional statements to see what needs to go in the where clause.

How do you guys deal with searches, in particular ones with mutiple criteria?

Thanks,

Rob

 

[ousoonerjoe]

IF statements.

Or I break it apart into multiple procs if it starts to get too cumbersome.

You can also create a variable for each optional field with a default value that will catch all (ie. '%'). Pass only the arguments you want to search against.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[TysonLPrice]

Nice timing on the question. Check this out:

http://www.sqlservercentral.com/articles/T-SQL/61918/

You may have to join but it's free and always has a new article every day.

 

[ca8msm]

I use this approach which I call "optional parameters", not sure if this is the correct term or not though.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[modika11]

Thanks for all your responses guys and thank you for the link Tyson was an interesting read.

 

[Chopstik]

Thanks for those links, Tyson and Mark. Both were helpful and gave me some direction into another problem I was trying to resolve (in the best way).

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[ESquared]

I encourage you all NOT to use dynamic SQL.

For more on the topic of using stored procedures for searches (and dynamic sql, too), see this thread. Then, search the web for "dynamic sql is bad" and read a few of the many many hits you'll get.

 

[Chopstik]

Don't worry... I am rather aware of the negative aspects of using dynamic SQL and was referring to other options. But thanks for the reminder. ;-)

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike