title: How to use same sql query for multiple where clause in a procedure
body: <p>Query #1</p>
  
  <pre><code>SELECT 
      @IN_EMPCOUNT = COUNT (a.CH_EMPCODE) 
  FROM 
      BSC_REVIEW_EMP_MASTER a
  INNER JOIN 
      dbo.cmn_authorization b ON (CASE b.vc_bu WHEN '%' THEN a.BUSSCD ELSE b.vc_bu END) =  a.BUSSCD           
  WHERE 
      IN_REVIEW_NO = @in_review_no
      AND b.ch_empcode = @ch_empcode  
      AND b.in_rolecode IN (136, 137, 138)
  </code></pre>
  
  <p>Query #2</p>
  
  <pre><code>SELECT 
      @IN_EMPCOUNT1 = COUNT (a.CH_EMPCODE) 
  FROM 
      BSC_REVIEW_EMP_MASTER a
  INNER JOIN 
      dbo.cmn_authorization b ON (CASE b.vc_bu WHEN '%' THEN a.BUSSCD ELSE b.vc_bu END) =  a.BUSSCD           
  WHERE 
      IN_REVIEW_NO = @in_review_no
      AND b.ch_empcode = @ch_empcode  
      AND b.in_rolecode IN (138, 139, 140)
  </code></pre>
  
  <p>Query #3</p>
  
  <pre><code>SELECT 
      @IN_EMPCOUNT2 = COUNT (a.CH_EMPCODE) 
  FROM 
      BSC_REVIEW_EMP_MASTER a
  INNER JOIN 
      dbo.cmn_authorization b ON (CASE b.vc_bu WHEN '%' THEN a.BUSSCD ELSE b.vc_bu END) =  a.BUSSCD           
  WHERE 
      IN_REVIEW_NO = @in_review_no
      AND b.ch_empcode = @ch_empcode  
      AND b.in_rolecode IN (141, 142, 145)
  </code></pre>
  
  <p>Result:    </p>
  
  <pre><code>Select @in_empcount, @in_empcount1, @in_empcount2
  </code></pre>
  
  <p>This is an example query on which I am working on. Can I do something like don't have to repeat the same select statements for every where clause <code>for (b.in_rolecode)</code> and result would be the same?</p>