How to use same sql query for multiple where clause in a procedure -

query #1

select      @in_empcount = count (a.ch_empcode)       bsc_review_emp_master inner join      dbo.cmn_authorization b on (case b.vc_bu when '%' a.busscd else b.vc_bu end) =  a.busscd                 in_review_no = @in_review_no     , b.ch_empcode = @ch_empcode       , b.in_rolecode in (136, 137, 138) 

query #2

select      @in_empcount1 = count (a.ch_empcode)       bsc_review_emp_master inner join      dbo.cmn_authorization b on (case b.vc_bu when '%' a.busscd else b.vc_bu end) =  a.busscd                 in_review_no = @in_review_no     , b.ch_empcode = @ch_empcode       , b.in_rolecode in (138, 139, 140) 

query #3

select      @in_empcount2 = count (a.ch_empcode)       bsc_review_emp_master inner join      dbo.cmn_authorization b on (case b.vc_bu when '%' a.busscd else b.vc_bu end) =  a.busscd                 in_review_no = @in_review_no     , b.ch_empcode = @ch_empcode       , b.in_rolecode in (141, 142, 145) 

result:

select @in_empcount, @in_empcount1, @in_empcount2 

this example query on working on. can don't have repeat same select statements every clause for (b.in_rolecode) , result same?

is you're asking -- including values in in clause:

select name  tabl1  id in ( 1,2,3,4,8,9 ) 

the in clause works or clause, no need separate queries.

edit -- based on newest edits, you're looking use case , count (you use sum, works same way):

select      count (case when b.in_rolecode in (136,137,138) 1 end) in_empcount,     count (case when b.in_rolecode in (138,139,140) 1 end) in_empcount1,     count (case when b.in_rolecode in (141,142,145) 1 end) in_empcount2 bsc_review_emp_master     inner join dbo.cmn_authorization b          on (case b.vc_bu when '%' a.busscd else b.vc_bu end) =  a.busscd            in_review_no = @in_review_no     , b.ch_empcode =@ch_empcode