StuartMc77
asked on
Whats wrong with this SQL statement?
I am trying to use a CASE statement in a WHERE clause, I have it to this point but when I try this I get the error "Incorrect syntax near the keyword 'END'". And if I remove the single quotes surrounding the (companid=0....) I get a message saying the '=' is wrong.
I'm stuck... lol
p.s. This is the code in a stored procedure and all the variables are declared and correct.
I'm stuck... lol
p.s. This is the code in a stored procedure and all the variables are declared and correct.
BEGIN
SELECT
count (DISTINCT (dbo.Member.ID)) AS mcount
FROM
dbo.Member
WHERE
((SELECT count(billingid) FROM billingdata
WHERE ((billingmonth>=@MONTH AND billingyr=@YEAR-1) OR (billingmonth<@MONTH AND billingyr=@YEAR)) AND (billingid=dbo.member.id)) >= 1
) AND
member.memberstatus=101 AND
CASE
WHEN @mtype='M' THEN '(companyid=0 AND departmentid=0 AND householdid=0)'
WHEN @mtype='J' THEN '(companyid=0 AND departmentid=0 AND householdid>0)'
WHEN @mtype='C' THEN '(companyid>0 AND departmentid=0 AND householdid=0)'
WHEN @mtype='D' THEN '(companyid>0 AND departmentid>0 AND householdid=0)'
END
END
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER