Case statement in a Stored Procedure

Just wondering if it is possible to use the case statement to determine what to execute in a stored procedure.

The code snippet shows an example of what I'm trying to do. The select statements are the same because I haven't finished them yet, so please ignore that, as they will eventually be different when I get them finished.
BEGIN
	CASE @SearchBy
	WHEN 'Out' THEN
		SELECT DISTINCT 
				Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
		FROM    Deployment INNER JOIN
				Event ON Deployment.Evnt_ID = Event.Evnt_ID
		WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND 
				(Deployment.CreatedOn > '1/1/2008')
		GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
		ORDER BY Event.Evnt_Start_Date
	WHEN 'Paid' THEN
		SELECT DISTINCT 
				Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
		FROM    Deployment INNER JOIN
				Event ON Deployment.Evnt_ID = Event.Evnt_ID
		WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND 
				(Deployment.CreatedOn > '1/1/2008')
		GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
		ORDER BY Event.Evnt_Start_Date
	ELSE	
		SELECT DISTINCT 
				Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
		FROM    Deployment INNER JOIN
				Event ON Deployment.Evnt_ID = Event.Evnt_ID
		WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND 
				(Deployment.CreatedOn > '1/1/2008')
		GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
		ORDER BY Event.Evnt_Start_Date
	END	
END
 
I get the following errors:
Msg 156, Level 15, State 1, Procedure SP_AdminWeb_DeploymentFees_Select, Line 20
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure SP_AdminWeb_DeploymentFees_Select, Line 30
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure SP_AdminWeb_DeploymentFees_Select, Line 39
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure SP_AdminWeb_DeploymentFees_Select, Line 49
Incorrect syntax near the keyword 'END'.

Open in new window

LVL 3
quanmacAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
try this:

BEGIN
        IF @SearchBy = 'Out'
            BEGIN
                SELECT DISTINCT
                                Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
                FROM    Deployment INNER JOIN
                                Event ON Deployment.Evnt_ID = Event.Evnt_ID
                WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND
                                (Deployment.CreatedOn > '1/1/2008')
                GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
                ORDER BY Event.Evnt_Start_Date
        END
            IF @SearchBy =  'Paid'
            BEGIN
                SELECT DISTINCT
                                Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
                FROM    Deployment INNER JOIN
                                Event ON Deployment.Evnt_ID = Event.Evnt_ID
                WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND
                                (Deployment.CreatedOn > '1/1/2008')
                GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
                ORDER BY Event.Evnt_Start_Date
        END
            IF @SearchBy NOT IN('Paid', 'Out')
            BEGIN    
                SELECT DISTINCT
                                Deployment.Evnt_ID, Event.Evnt_Start_Date, MAX(Deployment.CreatedOn) AS CreatedOn
                FROM    Deployment INNER JOIN
                                Event ON Deployment.Evnt_ID = Event.Evnt_ID
                WHERE   (Deployment.Evnt_ID IS NOT NULL) AND (Event.Evnt_Status = 'Canceled') AND
                                (Deployment.CreatedOn > '1/1/2008')
                GROUP BY Deployment.Evnt_ID, Event.Evnt_Start_Date
                ORDER BY Event.Evnt_Start_Date
        END    
END
0
 
quanmacAuthor Commented:
Thanks chapmandew,

That is what I had originally planned to do, but I wanted to see if the Case statement would work.

I did some more reading on the Case statement and found out that it can only be used to return a value and not to be used to determine which statements to run. http://p2p.wrox.com/topic.asp?TOPIC_ID=43503
0
 
spratkeCommented:
Just curious, why wouldn't you use an If, Else If, Else rather then three if statements?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
quanmacAuthor Commented:
There is no Else If in TSQL
0
 
spratkeCommented:
That makes sense, I just saw this in an MS SQL thread.
0
 
spratkeCommented:
That makes sense, I just saw this in an MS SQL thread.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.