Caveat: A fellow developer Allen Underwood demonstrated the perfection of this clauseto me a year ago.
Teddy On 12/12/06, Teddy Payne <[EMAIL PROTECTED]> wrote: > > This can be accomplished in SQL Server without dynamically writing the > stored procedure. > > You have to pass in the maximum number of parameters every time to the > stored procedure. To do this, you cfparam all of the proc params above the > stored procedure call. Inside of the stored procedure, you detect for the > default values of each paramater. If the default value is there, you set > the sql variable to null. > > Now the rea magic is where you use the variable inside of the queries: > > if @foo = 0 > begin > select @foo = null > end > > select > * > from > bar b > where > IsNull(b.foo,'') = IsNull(NullIf(@foo,''),IsNull(b.foo,'')) > > If @foo is null, the where clause is never executed and all values are > returned. If @foo has a value, the where clause is executed and you have a > filtered data. > > So in short, you can any number of variables passed as long as you pass a > default value that you detect for inside the procedure. It is also not > dynamically written so that the execution plan will stay consistent. > > Teddy > > > > > > > On 12/12/06, Andy Mcshane <[EMAIL PROTECTED]> wrote: > > > > Before I spend a load of time that I really do not have available at > > present experimenting with this I thought that I would ask the question > > here. I was wondering if anybody has ever dynamically built a cfstoredproc > > tag and then executed it. The situation I have is that I have ben asked if > > it is possible to dynamically build a cfstoredproc tag on the fly when you > > only know the number and type of parameters and SQL stored procedure name at > > run time. Has anybody ever done anything like this before? > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:263719 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
