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
