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:263718
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
