This may help you depending on the usage/type of where if's you are wanting to execute.
Create a temporary table with result A Join Temporary table with Result B <> to result A Ron -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, February 24, 2005 3:03 PM To: SQL Subject: Re: How do you do an IF condition in where statement Hi Bryan, The only problem is sometimes there are up to 10 conditions in the WHERE statement, and any of the 10 could be used depending on which fields someone fills out on a search form. So doing IF statements with a different query for every variation of those 10 conditions would be a bit tricky.. I thought there might be a way to actually process the IF statement only if the value for that var is not null. Its so easy to do in CF and yet seems such a pain in the ass in TSQL. Thanks for your feedback though. Take care, Pardeep. ----- Original Message ----- From: "Bryan Stevenson" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Thursday, February 24, 2005 11:38 AM Subject: Re: How do you do an IF condition in where statement > Pardeep....yer obviously not getting the answer here....so I went to > Google...first result has your answer: > > searched for "conditional stored procedure MS SQL" > > Read the forum posts...I'd us the example with 3 conditions with 3 different > queries depending on the condition that is met. I've used this approach > before and it works flawlessly....the benfit is explained near the bottom of > the page (better use of indexes). > > The example is not exactly like your...but it shows how to construct the > stored proc. > > HTH > > Cheers > > Bryan Stevenson B.Comm. > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > phone: 250.480.0642 > fax: 250.480.1264 > cell: 250.920.8830 > e-mail: [EMAIL PROTECTED] > web: www.electricedgesystems.com > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "SQL" <[email protected]> > Sent: Thursday, February 24, 2005 11:33 AM > Subject: Re: How do you do an IF condition in where statement > > > > No buddy, how do you do it in TSQL for a stored procedure:) > > > > > > ----- Original Message ----- > > From: "Venable, John" <[EMAIL PROTECTED]> > > To: "SQL" <[email protected]> > > Sent: Thursday, February 24, 2005 11:24 AM > > Subject: RE: How do you do an IF condition in where statement > > > > > >> You need single quotes around your values. > >> > >> i.e. lastname='#form.lastname#' > >> > >> John Venable > >> > >> > -----Original Message----- > >> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > >> > Sent: Thursday, February 24, 2005 2:26 PM > >> > To: SQL > >> > Subject: How do you do an IF condition in where statement > >> > > >> > How would you write this regular Cold Fusion query in TSQL for a > >> > stored > >> > procedure: > >> > > >> > select * > >> > from profiles > >> > where 0=0 > >> > <cfif form.firstname neq ''>and > >> > firstname=#form.firstname#</cfif> <cfif form.lastname neq ''>and > >> > lastname=#form.lastname#</cfif> <cfif form.phone neq ''>and > >> > phone=#form.phone#</cfif> > >> > > >> > I tried a few times, I can't quite get the hang of IF statements in > >> > TSQL for some reason, even tho. in CFMX they're juz a piece of cake. > >> > > >> > Thanks, > >> > Pardeep. > >> > > >> > > >> > > >> > >> > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2177 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
