multiple where clauses based on a parameter value

[ktucci]

i am not a SQL expert by any means but i do need to convert this CF SQL statement into a stored proc...i have tried using 'case when' with no luck and i have tried building a string and then executing the sp...the problem is down by the where clause...is there something i am missing...any help will be appreciated

<cfquery>
select
orders.qtylocal,
orders.qtyld,
orders.totalerv,
orderdetail.qty,
plans.description,
plans.producttype
FROM
((orders LEFT JOIN orderdetail ON orders.id = orderdetail.orderid) LEFT JOIN plans ON orderdetail.productid = plans.id) INNER JOIN SalesReps ON orders.sellerid = SalesReps.salesrep_id
where
0=0
<cfif #filter# eq &quot;voice&quot;>
and (orders.qtylocal + orders.qtyld <> 0)
</cfif>
<cfif #filter# eq &quot;dsl&quot;>
and orderdetail.qty <> 0 and orderdetail.productid in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)
</cfif>
<cfif #filter# eq &quot;internet&quot;>
and orderdetail.qty <> 0 and orderdetail.productid NOT in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)
</cfif>
<cfif #filter# eq &quot;tollfree&quot;>
and (orders.new800 or orders.existing800)
</cfif>
<cfif #filter# eq &quot;callingcards&quot;>
and orders.callingcards
</cfif>
<cfif #filter# eq &quot;mac&quot;>
and (orders.picchange or orders.hunt or orders.repics or orders.listings or orders.remoteforward or orders.voicemail or orders.callforward or orders.a800resporg or orders.callerid or orders.other or orders.moves or orders.lpic)
</cfif>
<cfif #filter# eq &quot;complex&quot;>
and (orders.complexorder)
</cfif>
</cfquery>

 

[tlbroadbent]

Your SP should look like this.

Create usp_myproc @filter varchar(12)

Declare @sql varchar(1024)
Select @sql=
'select
orders.qtylocal,
orders.qtyld,
orders.totalerv,
orderdetail.qty,
plans.description,
plans.producttype
FROM
((orders LEFT JOIN orderdetail ON orders.id = orderdetail.orderid) LEFT JOIN plans ON orderdetail.productid = plans.id) INNER JOIN SalesReps ON orders.sellerid = SalesReps.salesrep_id
where
0=0'
If @filter='voice'
Begin
+ 'and (orders.qtylocal + orders.qtyld <> 0)'
End
If @filter='dsl'
Begin
+ 'and orderdetail.qty <> 0 and orderdetail.productid in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)'
End
If @filter='internet'
Begin
+ 'and orderdetail.qty <> 0 and orderdetail.productid NOT in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)'
End
.
.
.

Exec(@sql) Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Actually, using a CASE statement would be cleaner.

Create usp_myproc @filter varchar(12)

Declare @sql varchar(1024)
Select @sql=
'select
orders.qtylocal,
orders.qtyld,
orders.totalerv,
orderdetail.qty,
plans.description,
plans.producttype
FROM
((orders LEFT JOIN orderdetail ON orders.id = orderdetail.orderid) LEFT JOIN plans ON orderdetail.productid = plans.id) INNER JOIN SalesReps ON orders.sellerid = SalesReps.salesrep_id
where
0=0 ' +
Case @filter
When 'voice' Then 'and (orders.qtylocal + orders.qtyld <> 0)'
When 'dsl' Then 'and orderdetail.qty <> 0 and orderdetail.productid in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)'
When 'internet' Then 'and orderdetail.qty <> 0 and orderdetail.productid NOT in (337,153,176,198,158,268,154,181,159,179,209,292,269,150,177,202,292,269,150,177,202,155,346,265,151,180,156,274,152,199,211,157,195,194,196)'
.
.
.
Else ''
End

Exec(@sql) Terry

http://members.home.net/tlbroadbent/index.htm

 

[ktucci]

it worked great

Thanks

Keith