title: Dynamic where clause inside a case statement
body: <p>I am stucked at a dynamic where clause inside case statement. Here is a block of my sql.</p>
  
  <pre><code>Select EstimatedCharges = CASE 
     WHEN EXISTS (
          SELECT 1
          FROM ResidualOverrideConfiguration
          WHERE FacilityCode = @FacilityCode
          AND DRGCode = DRG.DRG AND COALESCE(IsPayorPlanEstimateEnabled, 1) = 1
          AND ChargeAmount IS NOT NULL
          AND COALESCE(FacilityPlanCode, @PrimaryPlanCode) = @PrimaryPlanCode
          AND COALESCE(PatientType, @PatientType) = @PatientType
          AND COALESCE(FacilityPatientType, @FacilityPatientType) = @FacilityPatientType
          AND COALESCE(ServiceFieldCode, @ServiceFieldCode) = @ServiceFieldCode
      )
      THEN (PICK_SOME_FIELD)
    FROM SOME_TABLE
  </code></pre>
  
  <p>Below is my table ,</p>
  
  <p><img src="https://i.stack.imgur.com/CA4MX.png" alt="enter image description here"></p>
  
  <p>In this table <code>FacilityPlanCode,ServiceFieldCode,FacilityPatientType,PatientType</code> are optional field that are in use. <code>FacilityCode,DRGCode,ChargeAmount and IsPayorPlanEstimateEnabled</code> are mandatory. </p>
  
  <p>My requirement is that if the optional fields are coming null or blank then it should not be a part of where condition. </p>
  
  <p>For example if <code>FacilityPlancode= null, PatientType=Null</code> then the Select query inside the case will act like this, </p>
  
  <pre><code>SELECT 1
  FROM ResidualOverrideConfiguration
  WHERE FacilityCode = @FacilityCode
      AND DRGCode = DRG.DRG
      AND COALESCE(IsPayorPlanEstimateEnabled, 1) = 1
      AND ChargeAmount IS NOT NULL
      AND COALESCE(FacilityPatientType, @FacilityPatientType) = @FacilityPatientType
      AND COALESCE(ServiceFieldCode, @ServiceFieldCode) = @ServiceFieldCode
  </code></pre>
  
  <p>Help me out.</p>
  
  <h2>WHAT I NEED</h2>
  
  <p>If optional fields are coming null, then I need a way to completely ignore them, assuming they are not in clause.</p>