#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3

    Or clause as a variable


    to make the long story short, here is my query

    CREATE PROCEDURE dbo.SP_INSERTVALUESINTOPR
    (
    @SETUP AS VARCHAR(1),
    @DATEFROM AS SMALLDATETIME,
    @DATETO AS SMALLDATETIME,
    @PAYPROC AS VARCHAR(1),
    @RANK AS VARCHAR(1),
    @RANKVAL AS VARCHAR(10),
    @PAYPROCESS AS INT,
    @FILTER AS VARCHAR(8000)
    )



    AS



    IF @PAYPROCESS = 1
    BEGIN




    INSERT INTO PayRegister(emp_cde,div_cde,lname,fname,mname,setup,abs_days,date_from, date_to,cov_mnth,net_pres_days,payproc,rank,basicrate,undrtme_hrs, undrtme_min,basic_cola_amt,sl_leave,sl_amt,vl_leave,vl_amt,reg_ot_hrs, reg_ot_min,reg_ot_amt,ndiff_hrs,ndiff_min,ndiff_amt,ndiff_ot_hrs,ndiff_ot_min, ndiff_ot_amt,sp_hol_hrs,sp_hol_min,sp_hol_amt,sp_hol_ot_hrs,sp_hol_ot_min, sp_hol_ot_amt,leg_hol_pre_hrs,leg_hol_pre_min,leg_hol_amt,leg_hol_ot_hrs, leg_hol_ot_min,leg_hol_ot_amt,sp_rest_hrs,sp_rest_min,sp_rest_amt,sp_rest_ot_hrs, sp_rest_ot_min,sp_rest_ot_amt,leg_rest_hrs,leg_rest_min,leg_rest_amt,leg_rest_ot_hrs, leg_rest_ot_min,leg_rest_ot_amt,sun_hrs,sun_min,sun_amt,suot_hrs,suot_min,suot_amt, othr_allwnce,FL,FL_Amt,FLSUBSIDY,ol_leave,ol_amt,CL,CL_Amt,EL,EL_Amt,UL,ul_amt,gross_pay, sss_er_contri,sss_ee_contri,med_er_contri,med_ee_contri,ECC,hdmf_er_contri,hdmf_ee_contri, tax_inc,withholding_tax,sss_loan_dduc,hdmf_loan_dduc,co_loan_dduc,due_amt,cl_abs,cl_abs_amt,net_pay, username,taxable)
    SELECT emp_cde,div_cde ,
    isnull((SELECT max(last_nme) FROM employee WHERE emp_cde=Finhours.emp_cde),'') ,
    isnull((SELECT max(first_nme) FROM employee WHERE emp_cde=Finhours.emp_cde),'') ,
    isnull((SELECT max(MI) FROM employee WHERE emp_cde=Finhours.emp_cde),'') , @SETUP ,
    abs_days ,@DATEFROM,@DATETO,@DATETO, DaysPres,@PAYPROC,@RANK ,
    round(dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL),2) ,
    undrtme/60,cast(undrtme as int)%60 , dbo.COLA(emp_cde,@DATEFROM) , SL ,
    round(dbo.amount(SL,emp_cde,0),2) , VL , round(dbo.amount(VL,emp_cde,0),2) , regOT/60,regOT%60 ,
    round(dbo.GetRegOTAmount(emp_cde, regOT, @DATEFROM,@DATETO),2) , NytDif/60,NytDif%60 ,
    round(dbo.func_amount(emp_cde, nytdif, 'ND', nytdif, @DATEFROM,@DATETO),2) , NytDifOT/60,NytDifOT%60 ,
    round(dbo.func_amount(emp_cde, NytDifOT, 'NDOT', NytDifOT, @DATEFROM,@DATETO),2) , SHOL/60,SHOL%60 ,
    round(dbo.func_amount(emp_cde, SHOL, 'SHOL', SHOL, @DATEFROM,@DATETO),2) , SPOT/60,SPOT%60 ,
    round(dbo.func_amount(emp_cde, SPOT, 'SPOT', SPOT, @DATEFROM,@DATETO),2) , LHOL/60,LHOL%60 ,
    round(dbo.func_amount(emp_cde, LHOL, 'LHOL', LHOL, @DATEFROM,@DATETO),2) , LHOT/60,LHOT%60 ,
    round(dbo.func_amount(emp_cde, LHOT, 'LHOT', LHOT, @DATEFROM,@DATETO),2) , SRES/60,SRES%60 ,
    round(dbo.func_amount(emp_cde, SRES, 'SRES', SRES, @DATEFROM,@DATETO),2) , SROT/60,SROT%60 ,
    round(dbo.func_amount(emp_cde, SROT, 'SROT', SROT, @DATEFROM,@DATETO),2) , LRES/60,LRES%60 ,
    round(dbo.func_amount(emp_cde, LRES, 'LRES', LRES, @DATEFROM,@DATETO),2) , LROT/60,LROT%60 ,
    round(dbo.func_amount(emp_cde, LROT, 'LROT', LROT, @DATEFROM,@DATETO),2) , SUN/60,SUN%60 ,
    round(dbo.func_amount(emp_cde, SUN, 'SUN', SUN, @DATEFROM,@DATETO),2) , SUNOT/60,SUNOT%60 ,
    round(dbo.func_amount(emp_cde, SUNOT, 'SUNOT', SUNOT, @DATEFROM,@DATETO),2) , 0 , FL ,
    round(dbo.amount(FL,emp_cde,1),2) ,
    round(dbo.FLSUBSIDY(FL,emp_cde,1),2) , OL ,
    round(dbo.amount(OL,emp_cde,0),2) , CL ,
    round(dbo.amount(CL,emp_cde,0),2) , EL ,
    round(dbo.amount(EL,emp_cde,0),2) , UL ,
    round(dbo.amount(UL,emp_cde,0),2) ,
    round(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,S RES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO),2) ,
    round(dbo.Contribution(emp_cde,0,'SSSEmpr',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,ny tdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATE TO)),2) ,
    round(dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,ny tdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATE TO)),2) ,
    round(dbo.NewContribution(emp_cde,0,'PHICEmpr',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regO T,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@ DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)),2) ,
    round(dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regO T,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@ DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)),2) ,
    round(dbo.Contribution(emp_cde,0,'ECC',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif ,SL,VL,OL,FL,CL,EL,UL,0,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) ,
    round(dbo.HDMFContribution(emp_cde,'ER',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL ,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,L HOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) , round(dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL ,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,L HOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) ,
    round((dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT, SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)) - isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) - dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))),2) ,
    round(dbo.WithHoldingTax(emp_cde,(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL ,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO) - dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)) -
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) - dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)))),2) ,
    round(dbo.Loans(emp_cde,'0',@DATEFROM,@DATETO,'31'),2) ,
    round(dbo.Loans(emp_cde,'1',@DATEFROM,@DATETO,'31'),2) ,
    round(dbo.Loans(emp_cde,'2',@DATEFROM,@DATETO,'31'),2) ,
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) ,
    Clabs ,
    round(dbo.amount(Clabs,emp_cde,0),2) ,
    (round(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT, SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    (isnull(dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT, nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DA TETO)), 0)
    +
    isnull(dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,F L,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL, LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),0)
    +
    isnull(dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,reg OT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM, @DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)), 0)
    +
    0
    +
    round(dbo.WithHoldingTax(emp_cde,(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL ,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL))
    -
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0)
    -
    dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)))),2)
    +
    isnull(dbo.Loans(emp_cde,'0',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Loans(emp_cde,'1',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Loans(emp_cde,'2',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) ),2)) ,
    'SYSTEM',
    '1'
    FROM FinHours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =@FILTER )
    AND (rank='S' OR rank='JS' OR rank='SPSLT' OR rank='F' OR rank='JF' OR rank='SF')
    AND (rank <> 'AM' AND rank <> 'M' AND rank <> 'SM' AND rank <> 'AVP' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> 'XO4' AND div_cde <> 'XO5')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat='RS')


    END

    ELSE

    INSERT INTO PayRegister(emp_cde,div_cde,lname,fname,mname,setup,abs_days,date_from, date_to,cov_mnth,net_pres_days,payproc,rank,basicrate,undrtme_hrs, undrtme_min,basic_cola_amt,sl_leave,sl_amt,vl_leave,vl_amt,reg_ot_hrs, reg_ot_min,reg_ot_amt,ndiff_hrs,ndiff_min,ndiff_amt,ndiff_ot_hrs,ndiff_ot_min, ndiff_ot_amt,sp_hol_hrs,sp_hol_min,sp_hol_amt,sp_hol_ot_hrs,sp_hol_ot_min, sp_hol_ot_amt,leg_hol_pre_hrs,leg_hol_pre_min,leg_hol_amt,leg_hol_ot_hrs, leg_hol_ot_min,leg_hol_ot_amt,sp_rest_hrs,sp_rest_min,sp_rest_amt,sp_rest_ot_hrs, sp_rest_ot_min,sp_rest_ot_amt,leg_rest_hrs,leg_rest_min,leg_rest_amt,leg_rest_ot_hrs, leg_rest_ot_min,leg_rest_ot_amt,sun_hrs,sun_min,sun_amt,suot_hrs,suot_min,suot_amt, othr_allwnce,FL,FL_Amt,FLSUBSIDY,ol_leave,ol_amt,CL,CL_Amt,EL,EL_Amt,UL,ul_amt,gross_pay, sss_er_contri,sss_ee_contri,med_er_contri,med_ee_contri,ECC,hdmf_er_contri,hdmf_ee_contri, tax_inc,withholding_tax,sss_loan_dduc,hdmf_loan_dduc,co_loan_dduc,due_amt,cl_abs,cl_abs_amt,net_pay, username,taxable)
    SELECT emp_cde,div_cde ,
    isnull((SELECT max(last_nme) FROM employee WHERE emp_cde=Finhours.emp_cde),'') ,
    isnull((SELECT max(first_nme) FROM employee WHERE emp_cde=Finhours.emp_cde),'') ,
    isnull((SELECT max(MI) FROM employee WHERE emp_cde=Finhours.emp_cde),'') , @SETUP ,
    abs_days ,@DATEFROM,@DATETO,@DATETO, DaysPres,@PAYPROC,@RANK ,
    round(dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL),2) ,
    undrtme/60,cast(undrtme as int)%60 , dbo.COLA(emp_cde,@DATEFROM) , SL ,

    round(dbo.amount(SL,emp_cde,0),2) , VL , round(dbo.amount(VL,emp_cde,0),2) , regOT/60,regOT%60 ,
    round(dbo.GetRegOTAmount(emp_cde, regOT, @DATEFROM,@DATETO),2) , NytDif/60,NytDif%60 ,
    round(dbo.func_amount(emp_cde, nytdif, 'ND', nytdif, @DATEFROM,@DATETO),2) , NytDifOT/60,NytDifOT%60 ,
    round(dbo.func_amount(emp_cde, NytDifOT, 'NDOT', NytDifOT, @DATEFROM,@DATETO),2) , SHOL/60,SHOL%60 ,
    round(dbo.func_amount(emp_cde, SHOL, 'SHOL', SHOL, @DATEFROM,@DATETO),2) , SPOT/60,SPOT%60 ,
    round(dbo.func_amount(emp_cde, SPOT, 'SPOT', SPOT, @DATEFROM,@DATETO),2) , LHOL/60,LHOL%60 ,
    round(dbo.func_amount(emp_cde, LHOL, 'LHOL', LHOL, @DATEFROM,@DATETO),2) , LHOT/60,LHOT%60 ,
    round(dbo.func_amount(emp_cde, LHOT, 'LHOT', LHOT, @DATEFROM,@DATETO),2) , SRES/60,SRES%60 ,
    round(dbo.func_amount(emp_cde, SRES, 'SRES', SRES, @DATEFROM,@DATETO),2) , SROT/60,SROT%60 ,
    round(dbo.func_amount(emp_cde, SROT, 'SROT', SROT, @DATEFROM,@DATETO),2) , LRES/60,LRES%60 ,
    round(dbo.func_amount(emp_cde, LRES, 'LRES', LRES, @DATEFROM,@DATETO),2) , LROT/60,LROT%60 ,
    round(dbo.func_amount(emp_cde, LROT, 'LROT', LROT, @DATEFROM,@DATETO),2) , SUN/60,SUN%60 ,
    round(dbo.func_amount(emp_cde, SUN, 'SUN', SUN, @DATEFROM,@DATETO),2) , SUNOT/60,SUNOT%60 ,
    round(dbo.func_amount(emp_cde, SUNOT, 'SUNOT', SUNOT, @DATEFROM,@DATETO),2) , 0 , FL ,
    round(dbo.amount(FL,emp_cde,1),2) ,
    round(dbo.FLSUBSIDY(FL,emp_cde,1),2) , OL ,
    round(dbo.amount(OL,emp_cde,0),2) , CL ,
    round(dbo.amount(CL,emp_cde,0),2) , EL ,
    round(dbo.amount(EL,emp_cde,0),2) , UL ,
    round(dbo.amount(UL,emp_cde,0),2) ,
    round(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,S RES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO),2) ,
    round(dbo.Contribution(emp_cde,0,'SSSEmpr',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,ny tdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATE TO)),2) ,
    round(dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,ny tdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATE TO)),2) ,
    round(dbo.NewContribution(emp_cde,0,'PHICEmpr',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regO T,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@ DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)),2) ,
    round(dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regO T,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@ DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)),2) ,
    round(dbo.Contribution(emp_cde,0,'ECC',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif ,SL,VL,OL,FL,CL,EL,UL,0,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) ,
    round(dbo.HDMFContribution(emp_cde,'ER',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL ,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,L HOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) , round(dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL ,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,L HOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),2) ,
    round((dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT, SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)) - isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) - dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))),2) ,
    round(dbo.WithHoldingTax(emp_cde,(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL ,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO) - dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)) -
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) - dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)))),2) ,
    round(dbo.Loans(emp_cde,'0',@DATEFROM,@DATETO,'31'),2) ,
    round(dbo.Loans(emp_cde,'1',@DATEFROM,@DATETO,'31'),2) ,
    round(dbo.Loans(emp_cde,'2',@DATEFROM,@DATETO,'31'),2) ,
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) ,
    Clabs ,
    round(dbo.amount(Clabs,emp_cde,0),2) ,
    (round(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT, SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    (isnull(dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT, nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DA TETO)), 0)
    +
    isnull(dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,F L,CL,EL,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL, LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)),0)
    +
    isnull(dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,reg OT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM, @DATETO),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL)), 0)
    +
    0
    +
    round(dbo.WithHoldingTaxMonthly(emp_cde,(dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,O L,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)
    -
    dbo.Contribution(emp_cde,0,'SSSEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,S L,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO))
    -
    dbo.NewContribution(emp_cde,0,'PHICEmpy',@DATEFROM,dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytd if,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SRES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO ),dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL,UL))
    -
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0)
    -
    dbo.HDMFContribution(emp_cde,'EE',@DATEFROM,dbo.BasicPay(emp_cde,@DATEFROM,@DATETO,SL,VL,OL,FL,CL,EL ,UL),dbo.Grosspay(emp_cde,abs_days,dayspres,regOT,nytdif,SL,VL,OL,FL,CL,EL,UL,SHOL,SPOT,LHOL,LHOT,SR ES,LROT,SROT,LRES,SUN,SUNOT,NytDifOT,@DATEFROM,@DATETO)))),2)
    +
    isnull(dbo.Loans(emp_cde,'0',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Loans(emp_cde,'1',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Loans(emp_cde,'2',@DATEFROM,@DATETO,'31'),0)
    +
    isnull(dbo.Uniondues(emp_cde, @DATETO, @RANKVAL),0) ),2)) ,
    'SYSTEM',
    '1'
    FROM FinHours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =@FILTER)
    AND (rank='S' OR rank='JS' OR rank='SPSLT' OR rank='F' OR rank='JF' OR rank='SF')
    AND (rank <> 'AM' AND rank <> 'M' AND rank <> 'SM' AND rank <> 'AVP' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> 'XO4' AND div_cde <> 'XO5')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat='RS')
    GO



    my problem is, when i'm passing the @filter parameter, which is from a VB.net program, it does not run because of the where clause, if you could see, @filter is the where clause,

    the variable @filter would return somthing like...

    'AP0' OR div_cde = 'FO0' OR div_cde = 'ME0' OR div_cde = 'SO0' OR div_cde = 'XO0'


    the problem is the OR because it is nor highlighted as string in the variable...

    could you help me ASAP?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    you can not have WHERE( div_cde = @FILTER ) and @FILTER = 'AP0' OR div_cde = 'FO0' OR div_cde = 'ME0' OR div_cde = 'SO0' OR div_cde = 'XO0'
    system expect @FILTER as single value

    It is 2 solutions for that
    1. you can build sql string. Something like
    sqlstr='select * from yourtable where div_cde = ' + @FILTER
    and run execute(sqlstr)
    this will work, but it is complicated and open for SQL injection
    2. solution is better (in my opinion) change your filter value to delimited string like 'AP0','FO0','ME0' ,'SO0','XO0'
    create user defined function which split that string and return table
    and just join that table.it is a good article about that
    http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/ac500fbb-5955-4271-965b-0002b5cd7163/
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3
    Originally Posted by gk53
    you can not have WHERE( div_cde = @FILTER ) and @FILTER = 'AP0' OR div_cde = 'FO0' OR div_cde = 'ME0' OR div_cde = 'SO0' OR div_cde = 'XO0'
    system expect @FILTER as single value

    It is 2 solutions for that
    1. you can build sql string. Something like
    sqlstr='select * from yourtable where div_cde = ' + @FILTER
    and run execute(sqlstr)
    this will work, but it is complicated and open for SQL injection
    2. solution is better (in my opinion) change your filter value to delimited string like 'AP0','FO0','ME0' ,'SO0','XO0'
    create user defined function which split that string and return table
    and just join that table.it is a good article about that
    http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/ac500fbb-5955-4271-965b-0002b5cd7163/
    Sir if i would choose option one, then i have to pass them as string right? how about the string which i have used as parameters? should i put them as variables then add them to the query?


    for example, this line of code:
    round(dbo.func_amount(emp_cde, SRES, 'SRES', SRES, @DATEFROM,@DATETO),2)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    Yes. you pass your @filter as varchar(8000) or something like that.
    you can have @filter='round(dbo.func_amount(emp_cde, SRES, ''SRES'', SRES, @DATEFROM,@DATETO),2)'

    if it is your question. Just remember ' is string termination, so you should use '' instead of ' if it is just a character...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3
    Originally Posted by gk53
    Yes. you pass your @filter as varchar(8000) or something like that.
    you can have @filter='round(dbo.func_amount(emp_cde, SRES, ''SRES'', SRES, @DATEFROM,@DATETO),2)'

    if it is your question. Just remember ' is string termination, so you should use '' instead of ' if it is just a character...
    Sorry if i cant explain it the right way... i would try to use your suggestion sir...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3
    ok i have modified my code, and here it is.
    --simplified version
    declare @SqlString nvarchar(4000);
    declare @SqlParameter nvarchar(1000);
    declare @SETUP AS VARCHAR(1);
    declare @DATEFROM AS SMALLDATETIME;
    declare @DATETO AS SMALLDATETIME;
    declare @PAYPROC AS VARCHAR(1);
    declare @RANK AS VARCHAR(1);
    declare @RANKVAL AS VARCHAR(10);
    declare @PAYPROCESS AS INT;
    declare @FILTER AS VARCHAR(8000);

    set @SETUP = 'S'
    set @DATEFROM = '1/1/2012'
    set @DATETO = '1/15/2012'
    set @PAYPROC = 'S'
    set @RANK = 'R'
    set @RANKVAL = 'dues_amt_R'
    set @PAYPROCESS = 0
    set @FILTER = '''AP0'' OR div_cde = ''FO0'' OR div_cde = ''ME0'' OR div_cde = ''SO0'' OR div_cde = ''XO0'''

    set @SqlString =
    N'select * from finhours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde = @FILTER )
    AND (rank=''S'' OR rank=''JS'' OR rank=''SPSLT'' OR rank=''F'' OR rank=''JF'' OR rank=''SF'')
    AND (rank <> ''AM'' AND rank <> ''M'' AND rank <> ''SM'' AND rank <> ''AVP'' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> ''XO4'' AND div_cde <> ''XO5'')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat=''RS'')';

    set @SqlParameter = N'@SETUP AS VARCHAR(1),
    @DATEFROM AS SMALLDATETIME,
    @DATETO AS SMALLDATETIME,
    @PAYPROC AS VARCHAR(1),
    @RANK AS VARCHAR(1),
    @RANKVAL AS VARCHAR(10),
    @PAYPROCESS AS INT,
    @FILTER AS VARCHAR(8000)';

    EXEC sp_executesql @Sqlstring, @SqlParameter, @DATEFROM = @DATEFROM, @DATETO = @DATETO, @FILTER = @FILTER, @PAYPROC = @PAYPROC, @RANK = @RANK, @RANKVAL = @RANKVAL, @PAYPROCESS = @PAYPROCESS;


    now the problem is, there are errors which says,

    Server: Msg 170, Level 15, State 1, Line 15
    Line 15: Incorrect syntax near '='.
    Server: Msg 105, Level 15, State 1, Line 22
    Unclosed quotation mark before the character string ')'.


    but i couldn't find any...

    any suggestions sir to what caused this? i'm still trying to figure this one out btw
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    it is not valid statement
    Code:
    set @SqlString =
    N'select * from finhours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde = @FILTER )
    AND (rank=''S'' OR rank=''JS'' OR rank=''SPSLT'' OR rank=''F'' OR rank=''JF'' OR rank=''SF'')
    AND (rank <> ''AM'' AND rank <> ''M'' AND rank <> ''SM'' AND rank <> ''AVP'' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> ''XO4'' AND div_cde <> ''XO5'')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat=''RS'')';
    it should be like that
    Code:
    set @SqlString =
    N'select * from finhours
    WHERE( div_cde = ' + @FILTER + ')
    AND cdtfrom= ' + @DATEFROM + '
    AND cdtto=  ' + @DATETO +'
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =  ' + @FILTER + ')
    AND (rank=''S'' OR rank=''JS'' OR rank=''SPSLT'' OR rank=''F'' OR rank=''JF'' OR rank=''SF'')
    AND (rank <> ''AM'' AND rank <> ''M'' AND rank <> ''SM'' AND rank <> ''AVP'' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = ' + @FILTER + ') AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>= ' + @DATEFROM  +'
    AND date_to<= ' + @DATETO + '
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> ''XO4'' AND div_cde <> ''XO5'')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat=''RS'')';
    you should build complete SQL statement
    your statement

    Code:
    EXEC sp_executesql @Sqlstring, @SqlParameter, @DATEFROM = @DATEFROM, @DATETO = @DATETO, @FILTER = @FILTER, @PAYPROC = @PAYPROC, @RANK = @RANK, @RANKVAL = @RANKVAL, @PAYPROCESS = @PAYPROCESS;
    does not make sense ether...
    it should be just
    Code:
    exec  @Sqlstring
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3

    Wink


    Originally Posted by gk53
    it is not valid statement
    Code:
    set @SqlString =
    N'select * from finhours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde = @FILTER )
    AND (rank=''S'' OR rank=''JS'' OR rank=''SPSLT'' OR rank=''F'' OR rank=''JF'' OR rank=''SF'')
    AND (rank <> ''AM'' AND rank <> ''M'' AND rank <> ''SM'' AND rank <> ''AVP'' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> ''XO4'' AND div_cde <> ''XO5'')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat=''RS'')';
    it should be like that
    Code:
    set @SqlString =
    N'select * from finhours
    WHERE( div_cde = ' + @FILTER + ')
    AND cdtfrom= ' + @DATEFROM + '
    AND cdtto=  ' + @DATETO +'
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =  ' + @FILTER + ')
    AND (rank=''S'' OR rank=''JS'' OR rank=''SPSLT'' OR rank=''F'' OR rank=''JF'' OR rank=''SF'')
    AND (rank <> ''AM'' AND rank <> ''M'' AND rank <> ''SM'' AND rank <> ''AVP'' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = ' + @FILTER + ') AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>= ' + @DATEFROM  +'
    AND date_to<= ' + @DATETO + '
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> ''XO4'' AND div_cde <> ''XO5'')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat=''RS'')';
    you should build complete SQL statement
    your statement

    Code:
    EXEC sp_executesql @Sqlstring, @SqlParameter, @DATEFROM = @DATEFROM, @DATETO = @DATETO, @FILTER = @FILTER, @PAYPROC = @PAYPROC, @RANK = @RANK, @RANKVAL = @RANKVAL, @PAYPROCESS = @PAYPROCESS;
    does not make sense ether...
    it should be just
    Code:
    exec  @Sqlstring

    Sir thanks for all the replies you made so far sir, sir i'm using sp_executesql to execute my dynamic query sir, sp_executesql requires parameters which the fist is the @SQLstring, then the @parameters as the variables used, followed by their values. the problem is, sp_executesql requires the @SQLstring to be nvarchar, and nvarchar only holds 4000 characters...

    could i ask a simple query using exec @sqlstring? and could you add variables in it? and could i ask, what data type should i declare @sqlstring, i think my query is much larger than 8000 if i would declare it as varchar? and i'm using sql server 2000 too... thanks again! you are a great help
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    look on your original post you already have stored procedure and you can use @from as parameter....
    now you converted sql statement inside that stored procedure to just string with sql ststement, where all parameters included...
    and you again trying to use sp_executesql which exist just to prevent SQL injection if you using dynamic SQL statements and you get the same same issue again... it is no reason to sue that, just EXEC @Sqlstring
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    3
    Originally Posted by gk53
    look on your original post you already have stored procedure and you can use @from as parameter....
    now you converted sql statement inside that stored procedure to just string with sql ststement, where all parameters included...
    and you again trying to use sp_executesql which exist just to prevent SQL injection if you using dynamic SQL statements and you get the same same issue again... it is no reason to sue that, just EXEC @Sqlstring

    ---------------------------------------------------------------
    Sir the problem is, the stored proc does not return anything sir... so i'm trying to simulate it... so i used dynamic sql, the problem is in sql server 2000, varchar stores only 8000 characters? (please correct me if im wrong) and nvarchar stores 4000 characters my query is at... 16000 + characters, so i need a way on how pass my from clause to the query which would work...
    --------------------------------------------------------------
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    in this case that will not work for you. try another solution
    send @filter as 'AP0,FO0,ME0,SO0,XO0'
    and use code below to split values from @filter and insert into temporary table like in my code

    Code:
    DECLARE @NextString VARCHAR(40)
    DECLARE @Pos INT
    DECLARE @NextPos INT
    DECLARE @String VARCHAR(40)
    DECLARE @Delimiter VARCHAR(40)
    
    -- if @filter = 'AP0,FO0,ME0,SO0,XO0'
    --SET @String ='AP0,FO0,ME0,SO0,XO0'
    SET @String =@filter
    
    SET @Delimiter = ','
    SET @String = @String + @Delimiter
    SET @Pos = charindex(@Delimiter,@String)
    
    create table #parameters
    (
    	parameter varchar(50)
    )
    
    WHILE (@pos <> 0)
    BEGIN
    SET @NextString = substring(@String,1,@Pos - 1)
    insert into #parameters(parameter)
    values (@NextString)
    SET @String = substring(@String,@pos+1,len(@String))
    SET @pos = charindex(@Delimiter,@String)
    END 
    --select * from #parameters
    and in code from your original post

    Code:
    FROM FinHours
    WHERE( div_cde = @FILTER )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =@FILTER)
    AND (rank='S' OR rank='JS' OR rank='SPSLT' OR rank='F' OR rank='JF' OR rank='SF')
    AND (rank <> 'AM' AND rank <> 'M' AND rank <> 'SM' AND rank <> 'AVP' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> 'XO4' AND div_cde <> 'XO5')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat
    change to

    Code:
    FROM FinHours
    WHERE( div_cde in (select parameter from #parameters) )
    AND cdtfrom=@DATEFROM
    AND cdtto=@DATETO
    AND isnull((SELECT emp_cde FROM employee WHERE ( div_cde =@FILTER)
    AND (rank='S' OR rank='JS' OR rank='SPSLT' OR rank='F' OR rank='JF' OR rank='SF')
    AND (rank <> 'AM' AND rank <> 'M' AND rank <> 'SM' AND rank <> 'AVP' AND rank <> '')
    AND emp_cde=Finhours.emp_cde),'')= ''
    AND (SELECT MAX(basicrate) FROM employee WHERE ( div_cde = @FILTER ) AND emp_cde=FinHours.emp_cde) <> 1
    AND isnull((SELECT MAX(posted) FROM PayRegister WHERE emp_cde=Payregister.emp_cde
    AND date_from>=@DATEFROM
    AND date_to<=@DATETO
    AND div_cde=FinHours.div_cde),0) <> 2
    AND (div_cde <> 'XO4' AND div_cde <> 'XO5')
    AND emp_cde NOT IN (select distinct emp_cde from employee where emp_stat

IMN logo majestic logo threadwatch logo seochat tools logo