1
\$\begingroup\$

I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (of course it depends). I hope I could find alternative way for minimize my execution time or cursor complexity. Here is my query:

DECLARE @employeeid int;
DECLARE @joiningdate date;
DECLARE @quitdate date;
DECLARE @eodrefs int,@amounts decimal;

select @employeeid=HRMS_Employee.ID,@joiningdate=HRMS_Employee.Joining_Date,@quitdate=HRMS_Employee.QuitDate 
from HRMS_Employee where HRMS_Employee.Present_Status=1 and ID=4

DECLARE @tabEodRecord table(eodref  int,amount decimal)
insert INTO @tabEodRecord (eodref,amount)

select HRMS_EodRecord.Eod_RefFk,HRMS_EodRecord.ActualAmount from HRMS_EodRecord join HRMS_Employee on HRMS_EodRecord.EmployeeId=HRMS_Employee.ID 
where HRMS_EodRecord.Status=1 and HRMS_EodRecord.EmployeeId=@employeeid

IF CURSOR_STATUS('global','PayrollInfoCursor')>=-1
    BEGIN
     DEALLOCATE PayrollInfoCursor
    END

DECLARE PayrollInfoCursor CURSOR
FOR (select eodref,amount from @tabEodRecord) 
OPEN PayrollInfoCursor;
    FETCH NEXT FROM PayrollInfoCursor INTO  @eodrefs,@amounts
    WHILE @@FETCH_STATUS = 0
BEGIN
insert HRMS_PayrollInfo (PayrollFK,EmployeeIdFk,Eod_ReferenceFk,Amount,PaymentDate,[Status],AlertActive,Entry_By,Entry_Date,Update_By,Update_Date)
select 26,@employeeid,@eodrefs,@amounts,getdate(),1,1,1,getdate(),1,getdate()
FETCH NEXT FROM PayrollInfoCursor INTO  @eodrefs,@amounts
END
CLOSE PayrollInfoCursor  
DEALLOCATE PayrollInfoCursor

I hope there is something like this (however this won't work) because subquery returns multiple rows

insert HRMS_PayrollInfo (PayrollFK,EmployeeIdFk,Eod_ReferenceFk,Amount,PaymentDate,[Status],AlertActive,Entry_By,Entry_Date,Update_By,Update_Date)
select 26,@employeeid,(select eodref from tabEodRecord),(select amountfrom tabEodRecord),getdate(),1,1,1,getdate(),1,getdate()
\$\endgroup\$
0
\$\begingroup\$

From what I can tell based on the code you've presented, you can reduce it down to the following...

DECLARE @employeeid INT;

INSERT dbo.HRMS_PayrollInfo (PayrollFK, EmployeeIdFk, Eod_ReferenceFk, Amount, PaymentDate, Status, AlertActive, Entry_By, Entry_Date, Update_By, Update_Date)
SELECT
    26,
    @employeeid,
    er.Eod_RefFk,
    er.ActualAmount,
    GETDATE(),
    1,
    1,
    1,
    GETDATE(),
    1,
    GETDATE()   
FROM
    dbo.HRMS_EodRecord er
    --JOIN dbo.HRMS_Employee e
    --  ON er.EmployeeId = e.ID
WHERE
    er.Status = 1
    AND er.EmployeeId = @employeeid;
\$\endgroup\$
  • 1
    \$\begingroup\$ thanks. That's amazing. I didn't see actually how simple it is. thanks once again. \$\endgroup\$ – Parvez May 8 at 3:17
  • \$\begingroup\$ @Parvez - Not a problem. Sometimes it just takes a second set of eyes to see the problem from a different perspective. \$\endgroup\$ – Jason A. Long May 8 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.