SQL中游标使用的例子

CREATE PROCEDURE sp_appEmployeeCoverageEnrollment@newclass int,@employee_ssn int,@coverageID int,@coverageTierID int,@networkChoiceID int,@CoverageTypeID int,@Amount int,@Multiple floatasdeclare @EmployeeCoverageEnrollmentID as intdeclare @EFCOLifeNetwork as intDeclare EmpExists_cursor CURSOR Fast_FORWARD FORselect a.EmployeeCoverageEnrollmentIDfrom EmployeeCoverageEnrollment as a with (nolock) inner join coverage as b with (nolock) on a.coverageID = b.coverageIDwhere employee_ssn = @employee_ssn and b.coverageTypeID = @coverageTypeID

OPEN EmpExists_cursorFETCH NEXT FROM EmpExists_cursor into  @EmployeeCoverageEnrollmentIDif @@FETCH_STATUS = 0  begin delete from EmployeeCoverageEnrollment where EmployeeCoverageEnrollmentID = @EmployeeCoverageEnrollmentID  endclose EmpExists_Cursordeallocate EmpExists_cursor

set @EFCOLifeNetwork = 0if @coverageid=686 or (@coverageID>=688 and @coverageid<=699)  begin     set @EFCOLifeNetwork = @networkChoiceID     set @networkChoiceID = 0       end ------added for increase of amount on 11/22/2005if @EFCOLifeNetwork=1    begin       if @newclass=358 or @newclass=359 or @newclass=360            begin            if @Amount*0.1>10000              begin                 set @Amount=@Amount+@Amount*0.1              end   else              begin                set @Amount=@Amount+10000       end          end      if @newclass=361           begin     set @Amount=@Amount+10000   end   end -----added for increase of amount on 11/22/2005insert into EmployeeCoverageEnrollment(employee_ssn, coverageID, coverageTierID, networkChoiceID, amount, MultipleOfSalary, EFCOLifeNetwork)values(@employee_ssn, @coverageID, @coverageTierID, @networkChoiceID, @amount, @Multiple, @EFCOLifeNetwork)GO

 

 

 

 

 

 

 

select distinct aa.EMPLOYEE_SSN,aa.viseligdate,aa.vistermdate,aa.fname,aa.lname,aa.dob from (

 SELECT EMPLOYEE_SSN,fname,lname, (case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then viseligdate  else (select max(temptermthrudate)+1 as temptermthrudate  from  (SELECT temptermthrudate1 as temptermthrudate   FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 '   and temptermthrudate1<>'12/31/2020'   union   SELECT temptermthrudate2 as temptermthrudate   FROM Tbl_employee_eligibility   WHERE EMPLOYEE_SSN='493112829 '   and temptermthrudate2<>'12/31/2020'   union   SELECT temptermthrudate3 as temptermthrudate   FROM Tbl_employee_eligibility   WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate3<>'12/31/2020') as temp) end) as viseligdate, (case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then deneligdate  else (select max(temptermthrudate)+1 as temptermthrudate  from   (SELECT temptermthrudate1 as temptermthrudate   FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate1<>'12/31/2020'   union   SELECT temptermthrudate2 as temptermthrudate   FROM Tbl_employee_eligibility   WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate2<>'12/31/2020'   union   SELECT temptermfromdate3 as temptermthrudate   FROM Tbl_employee_eligibility   WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate3<>'12/31/2020') as temp) end) as deneligdate, vistermdate,dentermdate,dob  FROM Tbl_employee_eligibility  WHERE EMPLOYEE_SSN='493112829 '   union

 SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname, (case when temp_term_thru_date='12/31/2020' then viseligdate   else temp_term_thru_date+1 end) as viseligdate, (case when temp_term_thru_date='12/31/2020' then deneligdate   else temp_term_thru_date+1 end) as deneligdate, vistermdate,dentermdate,dob  FROM Tbl_dependents  WHERE EMPLOYEE_SSN='493112829'

) as aa  where  viseligdate<>'12/31/2020'group by aa.viseligdate,aa.vistermdate,aa.EMPLOYEE_SSN,aa.fname,aa.lname,AA.dob order by aa.viseligdate,aa.vistermdate,AA.dob

SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname, (case when temp_term_thru_date='12/31/2020' then viseligdate   else temp_term_thru_date+1 end) as viseligdate, (case when temp_term_thru_date='12/31/2020' then deneligdate   else temp_term_thru_date+1 end) as deneligdate, vistermdate,dentermdate,dob  FROM Tbl_dependents  WHERE EMPLOYEE_SSN='493112829' and viseligdate<>'12/31/2020'

viseligdatevistermdate

select * FROM Tbl_dependents  WHERE EMPLOYEE_SSN='493112829'

 

 

sp_slcMemoMaster 0,2900

select * from Tbl_Memo

 

 

 

 

 

 

select distinct aa.EMPLOYEE_SSN,aa.medeligdate,aa.medtermdate,aa.fname,aa.lname,aa.dob from (SELECT EMPLOYEE_SSN,fname,lname,(case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then medeligdate else (select max(temptermthrudate)+1 as temptermthrudate from(SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate2<>'12/31/2020' union SELECT temptermthrudate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate3<>'12/31/2020') as temp) end) as medeligdate,(case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then deneligdate else (select max(temptermthrudate)+1 as temptermthrudate from(SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate2<>'12/31/2020' union SELECT temptermfromdate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate3<>'12/31/2020') as temp) end) as deneligdate,medtermdate,dentermdate,dob FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' union SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname,(case when temp_term_thru_date='12/31/2020' then medeligdate else temp_term_thru_date+1 end) as medeligdate,(case when temp_term_thru_date='12/31/2020' then deneligdate else temp_term_thru_date+1 end) as deneligdate,medtermdate,dentermdate,dob FROM Tbl_dependents WHERE EMPLOYEE_SSN='" & session("ssn") & "') as aa  group by aa.medeligdate,aa.medtermdate,aa.EMPLOYEE_SSN,aa.fname,aa.lname,AA.dob order by aa.medeligdate,aa.medtermdate,AA.dob