存储过程 游标嵌套循环

ALTER PROCEDURE [dbo].[XXXXXX]

    

    

AS

    declare @startNum                nvarchar (255)

    declare @endNum                 nvarchar (255)

    declare @insurancetype            nvarchar (255)

    declare @company_ID                nvarchar(255)

    declare @unit_ID                nvarchar (255)

    declare @insuranceDivide_ID int

    declare @certificateNoClass        nvarchar(255)

    declare @st_nm int

    declare @end_nm int

    declare @cur_num                nvarchar(10)

    declare @currenDate                nvarchar(100) --保存当前时间

    declare @num_len int --变量长度

    declare @curren_len int --当前长度

    declare @i                        int --测试临时变量

    declare @temp                    nvarchar(8) --测试临时变量

    declare @temp_char                nvarchar(8) --测试临时变量

    

    --declare insur_cursor cursor for select startNum,endNum,insurancetype,Company_ID,unitID,certificateNoClass FROM InsuranceDivide where convert(int,endnum)-convert(int,startnum) -[usedAmount]>0

    declare insur_cursor cursor for select startNum,endNum,insurancetype,Company_ID,unitID,certificateNoClass,insuranceDivide_ID FROM InsuranceDivide where Company_ID='dtbx' and usedAmount<balance and insurancetype in ('142','209','239','240','241','242')

    

BEGIN

    

Open insur_cursor

fetch next from insur_cursor into @startNum,@endNum,@insurancetype,@company_ID,@unit_ID,@certificateNoClass,@insuranceDivide_ID

while @@fetch_status = 0

    begin

    

         set @currenDate=CONVERT(varchar(100), GETDATE(), 23)

            

            print @currenDate

            set @st_nm=convert(int,@startNum)

            set @end_nm=convert(int,@endNum)

            set @temp=0

            set @i=1;

            while @st_nm<=@end_nm

             begin                    

                    set @num_len=len(@endNum);

                    set @curren_len=len(@st_nm)                    

                    while @curren_len<(@num_len)

                     begin

                            set @temp_char=convert(nvarchar(8),@st_nm)

                            set @temp_char='0'+@temp_char                            

                            set @curren_len=@curren_len+1

                     end        

                    

                     declare t_cur cursor for select count(*) as total from insurance where ticketid=@temp_char and insurancetype=@insurancetype and company_id=Company_ID

                    

                    

                         open t_cur

                     fetch next from t_cur    into @cur_num    

                     if @cur_num=0

                     begin

                                 print @temp_char+' @cur_num '+@cur_num

                                 insert into invoiceNoManager(invoiceNo,certificateNoClass,InsuranceDivide_ID,SuspendType,unit_ID,company_ID,UsedStatus,CreateTime,LinesStatus,InsuranceType)

                        values(@st_nm,@certificateNoClass,@insuranceDivide_ID,2,@unit_ID,@company_ID,1,'2012-09-26',0,@insurancetype)

                                 fetch next from t_cur    into @cur_num

                     end

                     close t_cur

                     deallocate t_cur

                    

                            set @st_nm= @st_nm+1

             end

            

         fetch next from insur_cursor into @startNum,@endNum,@insurancetype,@company_ID,@unit_ID,@certificateNoClass,@insuranceDivide_ID

        

    end

    close insur_cursor

     deallocate insur_cursor

    

END