adding a second case to a where statement

ktucci · Apr 25, 2001

i am trying to add the commented cf code on the bottom the this sp...i assume it should be a case when but that does not seem to work...any help will be greatly appreciated

thanks in advance

CREATE Procedure sp_orderstatus

@filter varchar(12),
@statuslist varchar(12),
@mystartdate datetime,
@myenddate datetime

AS

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)'
When 'tollfree' Then 'and orders.new800 = 1 or orders.existing800 = 1'
When 'callingcards' Then 'and orders.callingcards = 1'
When 'mac' Then 'and orders.picchange = 1 or orders.hunt = 1 or orders.repics = 1 or orders.listings = 1 or orders.remoteforward = 1 or orders.voicemail = 1 or orders.callforward = 1 or orders.a800resporg = 1 or orders.callerid = 1 or orders.other = 1 or orders.moves = 1 or orders.lpic = 1'
When 'complex' Then 'and orders.complexorder = 1'

Else ''
End

/*

<cfswitch expression="#StatusList#">
<cfcase value="New">
and orders.entrydate between ###mystartdate### and ###myenddate###
</cfcase>
<cfcase value="Processed">
and orders.datesent between ###mystartdate### and ###myenddate###
</cfcase>
<cfcase value="Open">
and orders.datecancelled is null
and orders.completiondate is null
</cfcase>
<cfcase value="Complete">
and orders.completiondate between ###mystartdate### and ###myenddate###
</cfcase>

*/

Exec(@sql)

tlbroadbent · Apr 25, 2001

CREATE Procedure sp_orderstatus

@filter varchar(12),
@statuslist varchar(12),
@mystartdate char(10), /* Use char type rather than datetime */
@myenddate char(10) /* Assume dates in format mm/dd/yyyy */
.
.
.
When 'complex' Then 'and orders.complexorder = 1'

Else ''
End +

Case @StatusList
When 'New' Then " and orders.entrydate between '" + @startdate + "' and '" + @myenddate + "'"
When 'Processed' Then " and orders.datesent between '" @mystartdate "' and '" + @myenddate + "' and orders.datecancelled is null and orders.completiondate is null"
When 'Complete' Then " and orders.completiondate between '" + @mystartdate + "' and '" + @myenddate + "'"
Terry

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

ktucci · Apr 25, 2001

i am little confused with the use single and double quotes in your example...shouldnt it have single quotes around the and up to the plus...sorry if these questions are a little basic but i am not really a SQL programmer

thanks again

Case @StatusList
When 'New' Then " and orders.entrydate between '" + @startdate + "' and '" + @myenddate + "'"
When 'Processed' Then " and orders.datesent between '" @mystartdate "' and '" + @myenddate + "' and orders.datecancelled is null and orders.completiondate is null"
When 'Complete' Then " and orders.completiondate between '" + @mystartdate + "' and '" + @myenddate + "'"

tlbroadbent · Apr 25, 2001

I used that double quote/single quote combination because I wanted to enclose single quotes in the query - the dates required quotes. I prefer single quotes in a query. You can enclose the double quotes in the query. That certainly would be less confusing.

Revised T-SQL:

Case @StatusList
When 'New' Then ' and orders.entrydate between "' + @startdate + '" and "' + @myenddate + '"'
When 'Processed' Then ' and orders.datesent between "' @mystartdate '" and "' + @myenddate + '" and orders.datecancelled is null and orders.completiondate is null'
When 'Complete' Then ' and orders.completiondate between "' + @mystartdate + '" and "' + @myenddate + '"'

I would usually try to be consistent in use of single and double quotes. Terry

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

ktucci · Apr 25, 2001

this is how the whole thing looks...but i get an error 170..incorrect syntax near line 38 @mystartdate

any ideas

CREATE Procedure sp_orderstatus

@filter varchar(12),
@statuslist varchar(12),
@mystartdate datetime,
@myenddate datetime

AS

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)'
When 'tollfree' Then 'and orders.new800 = 1 or orders.existing800 = 1'
When 'callingcards' Then 'and orders.callingcards = 1'
When 'mac' Then 'and orders.picchange = 1 or orders.hunt = 1 or orders.repics = 1 or orders.listings = 1 or orders.remoteforward = 1 or orders.voicemail = 1 or orders.callforward = 1 or orders.a800resporg = 1 or orders.callerid = 1 or orders.other = 1 or orders.moves = 1 or orders.lpic = 1'
When 'complex' Then 'and orders.complexorder = 1'

Else ''
End+

Case @StatusList
When 'New' Then ' and orders.entrydate between "' + @mystartdate + '" and "' + @myenddate + '"'
When 'Processed' Then ' and orders.datesent between "' @mystartdate '" and "' + @myenddate + '" and orders.datecancelled is null and orders.completiondate is null'
When 'Complete' Then ' and orders.completiondate between "' + @mystartdate + '" and "' + @myenddate + '"'

Else ''
End

Exec(@sql)

tlbroadbent · Apr 25, 2001

The line is missing a + before @mystartdate.

TIP: If you double-click the error message in the Query Analyzer result pane, the cursor will move to the line in error. Terry

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

adding a second case to a where statement

ktucci

Programmer

tlbroadbent

MIS

ktucci

Programmer

tlbroadbent

MIS

ktucci

Programmer

tlbroadbent

MIS

Similar threads

Part and Inventory Search

Sponsor