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
Leave a Reply