Using Case in where clause

ALTER PROCEDURE [dbo].[usp_rpt_MilestoneSummaries]
    @IsLoan bit,
    @ProjectNumber  varchar(20),      
    @MilestoneType       varchar(max),
      @Success bit output,
      @Message varchar(1000) output       
AS
BEGIN
      BEGIN TRY
       IF EXISTS(SELECT 1 FROM Project WHERE ProjectNumber = @ProjectNumber)
          BEGIN             
                  SELECT                       
                          Project.ProjectNumber,
                          ProjectType.ProjectTypeId,
                          Project.ApplicantName,                       
                        ProjectAssignmentInformation.[Description],
                        Milestone.Name,
                    Milestone.[Description] As MilestoneDescription,               
                    ProjectMilestone.[Date],
                    ProjectMilestone.IsActual,
                    ProjectMilestone.IsTarget          
                    
                    --select * from ProjectMilestone
                  
                   FROM Project      
                           LEFT JOIN ProjectType
                                  ON (ProjectType.ProjectTypeId = Project.ProjectTypeId AND (ProjectType.IsLoan = @IsLoan))
                           LEFT JOIN ProjectAssignmentInformation         
                                     ON Project.ProjectId = ProjectAssignmentInformation.ProjectId
                           LEFT JOIN ProjectMilestone
                            ON Project.ProjectId = ProjectMilestone.ProjectId
                     LEFT JOIN Milestone
                            ON ProjectMilestone.MilestoneId = Milestone.MilestoneId
                                
                WHERE Project.ProjectNumber=@ProjectNumber AND  
                  @MilestoneType =
                (CASE WHEN @MilestoneType = 'Active' THEN 'ProjectMilestone.IsActual =1'
                          WHEN @MilestoneType = 'Target'  THEN 'ProjectMilestone.IsTarget =1'
                  ELSE 'Must Put Something HERE...'
                  END)
                                       
                        
              SET @Success = 1
                SET @Message = 'Successfully retrieved Milestone Summaries data by Project Number'        
         END

      END TRY
      
      BEGIN CATCH
      
                SELECT @Success = 0,
                         @Message = 'An error occurred retrieving Milestone Summaries data by Project Number, the error was:' + ERROR_MESSAGE()
      END CATCH
END

try to replace with

WHERE Project.ProjectNumber=@ProjectNumber 
AND  CASE 
      WHEN @MilestoneType = 'Active'  THEN 'ProjectMilestone.IsActual =1'
      WHEN @MilestoneType = 'Target'  THEN 'ProjectMilestone.IsTarget =1'
END	

Select allOpen in new window

wait a minute! you want to actually check if ProjectMilestone.IsActual =1 or ProjectMilestone.IsTarget =1?

then you should use this:

WHERE Project.ProjectNumber=@ProjectNumber 
AND  CASE 
      WHEN @MilestoneType = 'Active'  THEN ProjectMilestone.IsActual 
      WHEN @MilestoneType = 'Target'  THEN ProjectMilestone.IsTarget
      else 0
END	 = 1

Select allOpen in new window

Well it resolved my syntax problems but did not fetch any result after the execution. I am supposed to get one result for each type actual or target.


My updated code

SELECT 			     
				  Project.ProjectNumber,
				  ProjectType.ProjectTypeId,
				  Project.ApplicantName,			      
			      ProjectAssignmentInformation.[Description],
			      Milestone.Name,
	              Milestone.[Description] As MilestoneDescription,	 
	              ProjectMilestone.ProjectId,        
	              ProjectMilestone.[Date],
	              ProjectMilestone.IsActual, 
	              ProjectMilestone.IsTarget           
	              
	              --SELECT * FROM ProjectMilestone where Projectid = 1113
	            
			 FROM Project	
				   LEFT JOIN ProjectType
				          ON (ProjectType.ProjectTypeId = Project.ProjectTypeId AND (ProjectType.IsLoan = @IsLoan))
				   LEFT JOIN ProjectAssignmentInformation	   
				 	      ON Project.ProjectId = ProjectAssignmentInformation.ProjectId 
				   LEFT JOIN ProjectMilestone
	                      ON Project.ProjectId = ProjectMilestone.ProjectId
	               LEFT JOIN Milestone
	                      ON ProjectMilestone.MilestoneId = Milestone.MilestoneId
					  
		   -- WHERE Project.ProjectNumber=@ProjectNumber AND  
		  --  CASE @MilestoneType		   
				--     WHEN 'Active'  THEN ProjectMilestone.IsActual
				--     WHEN 'Target'  THEN ProjectMilestone.IsTarget					 
			 --END  = 1	  
			 
			 WHERE Project.ProjectNumber=@ProjectNumber AND
				CASE 
					  WHEN @MilestoneType = 'Active'  THEN ProjectMilestone.IsActual 
					  WHEN @MilestoneType = 'Target'  THEN ProjectMilestone.IsTarget
					  else 0
				END	 = 1

Select allOpen in new window

without having your data, it is quite hard for us to help you. What do you get when you are trying to run the query from SSMS?

Sorry I cannot give you my data but when I altered my where clause it worked and fetched the right results

  WHERE Project.ProjectNumber=@ProjectNumber  
                AND ProjectMilestone.IsActual=1

so I still feel something is wrong with my where clause case statement

and what is the value of @MilestoneType ?

this is what I use to execute my Stored proc.
@MilestoneType = 'Actual' or @MilestoneType = 'Target'

USE [StateRevolvingFund]
GO

DECLARE      @return_value int,
            @Success bit,
            @Message varchar(1000)

EXEC      @return_value = [dbo].[usp_rpt_MilestoneSummaries]
            @IsLoan = 1,
            @ProjectNumber = N'7001-01',
            @MilestoneType = 'Actual',
            @Success = @Success OUTPUT,
            @Message = @Message OUTPUT

SELECT      @Success as N'@Success',
            @Message as N'@Message'

SELECT      'Return Value' = @return_value

GO

Argh,  Now I fee like an Idiot. Thanks Eric

Thanks to you both.

@Éric Moreau

I have one final question,

When the user passes @MilestoneType = 'Both'
I would like to remove all the filter from my case and list both actual and target. Any thoughts

easy!

WHERE Project.ProjectNumber=@ProjectNumber 
AND  CASE 
      WHEN @MilestoneType = 'Both' then 1
      WHEN @MilestoneType = 'Active'  THEN ProjectMilestone.IsActual 
      WHEN @MilestoneType = 'Target'  THEN ProjectMilestone.IsTarget
      else 0
END	 = 1

Select allOpen in new window

Brilliant Thanks Eric

You get gold for this one. Thanks for saving my day