Conditional where clause

Question

Post reply

Conditional where clause

Frank-486989

Old Hand

Points: 342
More actions
April 15, 2009 at 3:30 pm

#208257

Hi all,
Have a question about conditional where clauses with SQL 2005 express.
I have 4 tables, Incident, Site, Resident, Unit....Site, resident, unit are all inner joined to incident and i want to create 1 stored procedure that will allow me to specifiy:
@Incident_ID int = null
,@Res_id int = null
,@Unit_id int = null
and return a single incident (@incident_id = 5), or return a list of incidents matching either or both the unit or res id's specified.
Just wondering if it's going to force me to either dynamically do it (which I hate doing), or put a if condition and repeat the same query with 4 different where clauses?
Thanks
Frank

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi Frank

This becomes the "most wanted" these days... 🙂

You can handle this by OR within your WHERE clause:

WHERE (@incident_id NOT NULL AND incident_id = @incident_id

OR (@res_id IS NOT NULL AND res_id = @res_id)

OR (@unit_id IS NOT NULL AND unit_id = @unit_id)

But

For these kind of procedures you should use the RECOMPILE option for procedure creation to void wrong execution plans. Best performance will be reached with three procedures.

Greets

Flo

Frank-486989 Old Hand Points: 342 More actions · Answer 2

After I wrote that, I was looking at some of the other forum posts for inspiration and I'm leaning on what you said. Would likely be better to have 3 or 4 well tuned queries than 1 massive POS that's hard to debug and has questionable performance.

While I don't anticipate much of a performance issue, I've recently reviewed some of my earlier "git-r-done" code and was thinking of taking a "do it right" approach for this project. 😀

Thanks for the info.

Frank

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 3

To add to what Flo has shown you - if this is not going to be executed a lot, then the recompile option works well. But, if it is going to be executed many times a day - then you really want to create separate procedures or use dynamic SQL.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Frank-486989 Old Hand Points: 342 More actions · Answer 4

I dont anticipate it being called much since its more of a report type query, but I'm saying that today, who's knows whats going to happen 4 - 6 months down the line.

I do know I don't really want to go down the dynamic sql path if I can help it, so right now looking at doing it "best practices" the first time so I dont have to figure it out later on if the project gets more use than anticipated.

Thanks for the input, appreciate it a lot.

Frank