MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Or clause as a variable


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 7th, 2013, 11:54 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
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?
Reply With Quote
  #2  
Old January 8th, 2013, 10:38 AM
gk53 gk53 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 71 gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8
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/
Reply With Quote
  #3  
Old January 8th, 2013, 06:59 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
Quote:
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)
Reply With Quote
  #4  
Old January 9th, 2013, 07:40 AM
gk53 gk53 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 71 gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8
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...
Reply With Quote
  #5  
Old January 9th, 2013, 05:49 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
Quote:
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...
Reply With Quote
  #6  
Old January 10th, 2013, 09:01 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
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
Reply With Quote
  #7  
Old January 11th, 2013, 10:40 AM
gk53 gk53 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 71 gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8
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
Reply With Quote
  #8  
Old January 14th, 2013, 09:24 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
Wink
Quote:
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
Reply With Quote
  #9  
Old January 16th, 2013, 11:51 AM
gk53 gk53 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 71 gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8
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
Reply With Quote
  #10  
Old January 16th, 2013, 06:53 PM
narrokk narrokk is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 31 narrokk User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 42 sec
Reputation Power: 1
Quote:
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...
--------------------------------------------------------------
Reply With Quote
  #11  
Old January 17th, 2013, 08:31 AM
gk53 gk53 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 71 gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level)gk53 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8
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
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Or clause as a variable

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap