May 12, 2009 at 7:28 pm
CREATE PROC sp_LifeStageIdentifier
@LibraryID char(10)
AS
BEGIN
DECLARE @bookcount AS int
UPDATE Checkout
SET @bookcount = (SELECT COUNT(*) FROM Checkout WHERE @LibraryID = LibraryID)
SELECT Checkout =
CASE LifeStage
WHEN (@bookcount < 25) THEN (SET Lifestage = 'New')
WHEN (@bookcount > 25 and @bookcount < 100) AND
(DateDiff(dd,(SELECT DateAccountOpen FROM CardholderData d WHERE Checkout.LibraryID = d.LibraryID),GETDATE()) < 365 ) THEN (SET Lifestage = 'Emerging')
WHEN (@bookcount < 100) AND
(DateDiff(dd,(SELECT DateAccountOpen FROM CardholderData d WHERE Checkout.LibraryID = d.LibraryID),GETDATE()) > 365 ) THEN (SET Lifestage = 'Mature')
WHERE LibraryID <> NULL
END
The Error that i get is
Msg 102, Level 15, State 1, Procedure sp_LifeStageIdentifier, Line 10
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Procedure sp_LifeStageIdentifier, Line 12
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_LifeStageIdentifier, Line 14
Incorrect syntax near ','.
any help would be much obliged
May 12, 2009 at 8:00 pm
The are two different formats for the CASE function:
1:
CASE [valueExpr1] WHEN [valueExpr2] THEN [valueExpr3]
This format compares [valueExpr1] to the value expression after each WHEN clause and if true then returns [valueExpr3]
and:
2:
CASE WHEN [conditional_expression] THEN [valueExpr3]
This format has no CASE or WHEN value expressions and instead evaluates each WHENs conditional expression and if true then returns [valueExpr3].
If you look at your case function you will see some differences:
...
SELECT Checkout =
CASE LifeStage
WHEN (@bookcount < 25) THEN (SET Lifestage = 'New')
...
First you are mixing both formats together, you have a CASE value expression and a WHEN conditional exprssion. It's one format or the other, they don't mix.
Secondly, instead of a value expression after the THEN you are trying to execute a SET statement, which also is invalid.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 10:31 pm
you rock thanks!!
May 12, 2009 at 11:10 pm
Glad I could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply