vendredi 19 août 2016

Using two cursors in a stored procedure

I have a SP in which there is a cursor which loops and gives me the desired result.

Now what I want is, I want to use the same cursor twice so that the same record should get inserted into the inward_doc_tracking_trl twice

Here is my SP:-

ALTER procedure set_auto_action_1    
                    as       
                   begin 
                declare @inv_hdr_mkey int    
                declare @inv_trl_mkey nvarchar(200)    
                declare @entry_sr_no nvarchar(200)    
                declare @Nuser_mkey varchar(10)    
                declare @n_department varchar(10)    
                declare @cstatus_flag int    
                declare @remarks varchar(500)    
                declare @cuserid int    
                declare @u_datetime DATETIME    
                declare @nstatus_flag int    
                declare @delete_flag varchar(1)    
                declare @cdept_id int    
                declare @ref_mkey int    
                declare @No_Of_Days varchar(10)    
                declare @Approved_Amount numeric(18, 2)    
                declare @Chq_No varchar(10)    
                declare @Chq_dated DATETIME    
                declare @Chq_Bank varchar(10)    
                declare @Chq_Amount numeric(18, 2)    
                declare @Vendor_MKey varchar(10)    
                declare @Vendor_Comp_Mkey varchar(10)    
                declare @Project_Mkey varchar(10)    
                declare @Program_mkey varchar(10)    
                declare @Payment_MKey varchar(10)    
                declare @Due_Date datetime    
                declare @Updated_Remarks varchar(100)    
                declare @Updated_Bill_no varchar(10)    
                declare @Updated_Bill_Date datetime    
                declare @Updated_Bill_Amt numeric(18, 2)    
                declare @Party_Name varchar(100)    
                declare @Acc_mkey varchar(10)    
                declare @TotalDeductions numeric(18, 2)    
                declare @Broker_Mkey varchar(10)    
                declare @Customer_Mkey varchar(10)    
                declare @Payable_Amt numeric(18, 2)    
                declare @Balance_Amt numeric(18, 2)    
                set @Nuser_mkey = NULL    
                set @n_department = NULL    
                set @remarks = 'Document received'    
                set @u_datetime = getdate()    
                set @nstatus_flag=1
                set @cstatus_flag = 2    
                set @delete_flag = 'N'    
                set @No_Of_Days = NULL    
                set @Acc_mkey = NULL          

                declare acCur cursor for select 

                    mkey,status_flag,to_department,to_user,approved_amount,chq_no,chq_dated,chq_bank,chq_amount,vendor_mkey,
                    Vendor_Comp_Mkey,project_mkey,program_mkey,payment_mkey,due_date,updated_remarks,updated_bill_no,updated_bill_date,
                    updated_bill_amt,party_name,totaldeductions,Broker_Mkey,Customer_Mkey,Payable_Amt,balance_amt 
                    from inward_doc_tracking_hdr where status_flag not in (5,13,14)
                    and doc_date <  CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120) + ' 15:00:00')   

                open acCur    

                FETCH NEXT FROM acCur into  
                                    @inv_hdr_mkey,@cstatus_flag,@cdept_id,@cuserid,@Approved_Amount,
                                    @Chq_No,@Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,@Vendor_Comp_Mkey,
                                    @Project_Mkey,@Program_mkey,@Payment_MKey,@Due_Date,@Updated_Remarks,@Updated_Bill_no,
                                    @Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,@TotalDeductions,@Broker_Mkey,
                                    @Customer_Mkey,@Payable_Amt,@Balance_Amt        

                WHILE @@FETCH_STATUS = 0    
                BEGIN    

                                    select @inv_trl_mkey= max(mkey)+1 from inward_doc_tracking_trl     
                                    select @entry_sr_no = max(entry_sr_no)+1 from inward_doc_tracking_trl where ref_mkey=@inv_hdr_mkey



                                    insert into inward_doc_tracking_trl (Mkey,Entry_Sr_No,N_UserMkey,N_Department,
                                    CStatus_Flag,Remarks,
                                    CUser_ID,                           
                                    U_Datetime,NStatus_Flag,Delete_Flag,CDept_Id,Ref_Mkey,
                                    No_Of_Days,Approved_Amount,Chq_No,Chq_dated,Chq_Bank,Chq_Amount,Vendor_MKey,Vendor_Comp_Mkey,
                                    Project_Mkey,Program_mkey,Payment_MKey,Due_Date,Updated_Remarks,Updated_Bill_no,
                                    Updated_Bill_Date,Updated_Bill_Amt,Party_Name,Acc_mkey,TotalDeductions,Broker_Mkey,Customer_Mkey,
                                    Payable_Amt,Balance_Amt) 

                                    values 
                                    (@inv_trl_mkey,@entry_sr_no,@Nuser_mkey,@n_department,@cstatus_flag,
                                    @remarks,                           
                                    CASE WHEN (@cuserid IS NULL) THEN 0 ELSE @cuserid END,
                                    @u_datetime,@nstatus_flag,@delete_flag,
                                    CASE WHEN (@cdept_id IS NULL) THEN 0 ELSE @cdept_id END,
                                    @inv_hdr_mkey,
                                    @No_Of_Days,@Approved_Amount,@Chq_No,@Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,
                                    @Vendor_Comp_Mkey,@Project_Mkey,@Program_mkey,@Payment_MKey,@Due_Date,@Updated_Remarks,
                                    @Updated_Bill_no,@Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,@Acc_mkey,@TotalDeductions,
                                    @Broker_Mkey,@Customer_Mkey,@Payable_Amt,@Balance_Amt)    


                                    FETCH NEXT FROM acCur into @inv_hdr_mkey,@cstatus_flag,@cdept_id,@cuserid,@Approved_Amount,@Chq_No,
                                    @Chq_dated,@Chq_Bank,@Chq_Amount,@Vendor_MKey,@Vendor_Comp_Mkey,@Project_Mkey,@Program_mkey,@Payment_MKey,
                                    @Due_Date,@Updated_Remarks,@Updated_Bill_no,@Updated_Bill_Date,@Updated_Bill_Amt,@Party_Name,
                                    @TotalDeductions,@Broker_Mkey,@Customer_Mkey,@Payable_Amt,@Balance_Amt

                end    
                            close acCur    
                            deallocate acCur    
                end

kindly help me with how to use the same cursor twice in SP

Aucun commentaire:

Enregistrer un commentaire