Case when in where clause in sql server

In below example case when used for multiple condition in sql server

USE [PCCAS]
GO
/****** Object:  StoredProcedure [dbo].[sp_rpt_Outreach_Adhoc_Report]    Script Date: 05/21/2014 12:09:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[sp_rpt_Outreach_Adhoc_Report]

@AllTypeIds NVARCHAR(MAX),
@AllTrainerIds NVARCHAR(MAX),
@AllTopicIds NVARCHAR(MAX),
@AllAudienceIds NVARCHAR(MAX),
@NoofAttendees int,
@AtndessCond int,
@FromDate date,
@ToDate date,
@FieldsToDisp NVARCHAR(MAX),
@OrderBy NVARCHAR(MAX)


AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF

DECLARE @SQL nvarchar(MAX)

--SELECT CAST(items AS int)StsId INTO #tmpStsIds
--	 FROM dbo.fn_Split(@AllStatusIds,',')
--	 WHERE items<>'';
	
	SELECT items TypeId INTO #tmpTypeIds
	 FROM dbo.fn_Split(@AllTypeIds,',')
	 WHERE items<>'';
	 
	SELECT CAST(items AS int) TrainerId INTO #tmpTrainerIds
	 FROM dbo.fn_Split(ISNULL(@AllTrainerIds,''),',')
	 WHERE items<>''; 
	 
	 SELECT CAST(items AS int) TopicId INTO #tmpTopicIds
	 FROM dbo.fn_Split(ISNULL(@AllTopicIds,''),',')
	 WHERE items<>''; 
	 
	SELECT CAST(items AS int) AudienceId INTO #tmpAudienceIds
	 FROM dbo.fn_Split(ISNULL(@AllAudienceIds,''),',')
	 WHERE items<>''; 
	 
	 
 --select * from #tmpIds
 
select ISNULL(CONVERT(VARCHAR(10),o.outreach_CreatedDate,101),'') ReportDate,
o.outreach_OutreachType OutreachType,
dbo.fn_getUserNameById_Select(ISNULL(o.outreach_NameId,0)) Trainer,
dbo.dmnValue_Select(ISNULL(o.outreach_TopicId,0)) Topic,dbo.dmnValue_Select(ISNULL(o.outreach_AudienceId,0)) Audience,
ISNULL(dbo.dmnValue_Select(ISNULL(o.outreach_AreaId,0)),'') Area,
ISNULL(dbo.dmnValue_Select(ISNULL(o.outreach_LanguageId,0)),'') Language,
ISNULL((Select STUFF((SELECT ', ' + b.incident_CaseNum 

	FROM  dbo.outreachMC a,dbo.incident b
	WHERE a.outreachMC_IncidentId=b.incident_IncidentId
	AND a.outreachMC_outreach_OutreachId=o.outreach_OutreachId

    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')),'')  Incident,
ISNULL(o.outreach_NoOfAttendees,0) NoOfAttendees,'' GroupBy

--ISNULL(d.miscDemographics_FirstName,'') FirstName,
--ISNULL(d.miscDemographics_LastName,'') LastName,
--ISNULL(d.miscDemographics_MiddleInitial,'') MiddleInitial,
--v.[Date Of Birth] Dob,v.AKA,v.Gender,v.Language,v.Race,(ISNULL(a.miscAddress_Address1,'')+' '+ISNULL(a.miscAddress_City,'')+' '+ISNULL(a.miscAddress_StateCode,'')+' '+
--	ISNULL(a.miscAddress_County,'')+' '+ISNULL(a.miscAddress_Zip,'')) AS Address,dbo.fn_MiscPhoneNo_Select(v.Id) as Phone,
--	dbo.fn_MiscEmail_Select(v.Id) as Email,ISNULL(dbo.dmnValue_Select(ISNULL(s.sane_SaneStatusId,0)),'') [Status],
--	ISNULL(dbo.dmnValue_Select(ISNULL(s.sane_AttributesId,0)),'') [Type],
--	 ISNULL(CONVERT(VARCHAR(10),s.sane_CreatedDate,101),'') ReportDate,'' GroupBy

INTO #tmp 

FROM dbo.[outreach]  o 

LEFT OUTER JOIN #tmpTypeIds tp ON ISNULL(o.outreach_OutreachType,0)=tp.TypeId 
LEFT OUTER JOIN #tmpTrainerIds tr ON ISNULL(o.outreach_NameId,0)=tr.TrainerId  
LEFT OUTER JOIN #tmpTopicIds tpi ON ISNULL(o.outreach_TopicId,0)=tpi.TopicId  
LEFT OUTER JOIN #tmpAudienceIds au ON ISNULL(o.outreach_AudienceId,0)=au.AudienceId



WHERE o.outreach_OutreachType= CASE WHEN EXISTS(select * from #tmpTypeIds) THEN tp.TypeId ELSE o.outreach_OutreachType END 
AND o.outreach_NameId= CASE WHEN EXISTS(select * from #tmpTrainerIds) THEN tr.TrainerId ELSE o.outreach_NameId END 
AND o.outreach_TopicId= CASE WHEN EXISTS(select * from #tmpTopicIds) THEN tpi.TopicId ELSE o.outreach_TopicId END 
AND o.outreach_AudienceId= CASE WHEN EXISTS(select * from #tmpAudienceIds) THEN au.AudienceId ELSE o.outreach_AudienceId END 

AND 
CAST(o.outreach_CreatedDate AS date) BETWEEN CASE
										WHEN @FromDate IS NOT NULL THEN @FromDate
										    ELSE CAST(o.outreach_CreatedDate AS date)
										END AND CASE
											   WHEN @ToDate IS NOT NULL THEN @ToDate
												  ELSE CAST(o.outreach_CreatedDate AS date)
											   END
					
					AND ( 
					CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=1  THEN 
					o.outreach_NoOfAttendees ELSE 1 END =
				    CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=1  THEN 
					@NoofAttendees ELSE 1 END 
					
					AND  
					
					CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=0  THEN 
					o.outreach_NoOfAttendees ELSE 1 END < 
				    CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=0  THEN 
					@NoofAttendees ELSE 2 END 
					
					AND  
					
					CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=2  THEN 
					o.outreach_NoOfAttendees ELSE 1 END > 
				    CASE WHEN @NoofAttendees IS NOT NULL AND @AtndessCond=2  THEN 
					@NoofAttendees ELSE 0 END 
					
	                       )								   
										
			--AND 	  
			
			--(
			--CASE WHEN @NoofAttendees IS NULL  THEN o.outreach_NoOfAttendees =o.outreach_NoOfAttendees  END 
			
			--OR 1=1
			
			--)
			
			-- o.outreach_NoOfAttendees BETWEEN 
			--CASE 
			
			--WHEN @NoofAttendees IS NULL  THEN 
			--o.outreach_NoOfAttendees AND o.outreach_NoOfAttendees 
			--ELSE 1 AND 1 END 
			
											   
	--	AND ( CASE @NoofAttendees
		
	--	WHEN IS NULL THEN o.outreach_NoOfAttendees=o.outreach_NoOfAttendees
	-- WHEN 0  THEN  o.outreach_NoOfAttendees < @NoofAttendees
	--WHEN 1  THEN o.outreach_NoOfAttendees =@AtndessCond 
	--ELSE  o.outreach_NoOfAttendees > @AtndessCond END
		
	--	)									   
											   
	--AND o.outreach_NoOfAttendees  
	--CASE WHEN 
	--@NoofAttendees IS NULL THEN = 
	--WHEN @AtndessCond =1 THEN < END  
	
	
	
	
	
	

	--	select * from #tmp 
		
		--select 	FirstName,MiddleInitial from 	#tmp 							   
			--SELECT * INTO #TMP_SEARCH1 FROM #TMP_SEARCH WHERE 1<>1
			
		SET @SQL='SELECT '+@FieldsToDisp+' FROM #tmp ORDER BY '+@OrderBy 
		
		EXECUTE sp_executesql @SQL
			
											   
	 
END

You Might Also Like

Leave a Reply