Loop to update records in sql server

[barmatt80]

I finally got my call to a stored procedure on our db2 to work. However i might have to change what the stored procedure does, if I cannot get it to work how we want. Which i would like to make it work on the sql server side, and not have to change the db2 side as that takes some time from our developers.

But this is what i want to do. I have a stored procedure that calls the db2 stored procedure to return 4 leave amounts(@Annual , @Sick, @Family, @Other). @EmployeeID is the identifier to pull the appropriate record.

I have created a query to use the returned values and update the record in my database table(Maintbl).

So my question is is there a way to loop through all records in the database table, that would query the db2 stored procedure and then update the Maintbl table?

I have the query to update, but i have to specify the EmployeeID each time i run it. Just need away to loop through and then turn it into a nightly job to run, but looping through is the first step.

EmployeeID's are not sequential, random integers from 1 digit to 6 digits.

Help or suggestions?

Thank you.

[Jerry Winston]

can you show some of the code you have executing the stored procedure?

It think you have something like this maybe:


DECLARE @Annual INT
DECLARE @Sick INT
DECLARE @Family INT
DECLARE @Other INT

SP_DB2(@MyEmpID ,@Annual, @Sick, @Family, @Other)

update Maintbl
SET col1 =@Annual
SET col2 =@Sick
SET col3 =@Family
SET col4 =@Other
WHERE Maintbl.EID = @MyEmpID

and you want to loop the @MyEmpID values from your Maintbl table?

if this is close you can use a CURSOR to loop:
.
DECLARE @Annual INT
DECLARE @Sick INT
DECLARE @Family INT
DECLARE @Other INT
--This is not a typo. you don't include the @ when declaring
--CURSOR types
DECLARE myMaintblCursor CURSOR FOR
SELECT Maintbl.EID FROM Maintbl

OPEN myMaintblCursor

--Read the initial employee ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyEmpID

WHILE @@FETCH_STATUS = 0
BEGIN

SP_DB2(@MyEmpID ,@Annual, @Sick, @Family, @Other)

update Maintbl
SET col1 =@Annual
SET col2 =@Sick
SET col3 =@Family
SET col4 =@Other
WHERE Maintbl.EID = @MyEmpID

--get the next Employee ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyEmpID
END



let me know if this works for you.

[barmatt80]

Awesome Thanks! I should have been more clear.

I am rather stoked, as I was actually working on what you posted.

But I can get the first record to update and none of the rest. Don't know what I am doing wrong.

I'll post my code as soon as i can.

Thanks again!

[barmatt80]

Duhh, I figured it out... forgot the last line of code: INTO @MyEmpID.

I'll post my full code up later for others having such issues.

Takes 10 seconds to update 243 records. Don't know if that is good or bad. I would borderline on not good and not bad. I have asked the db2 team and the server guys if they can live with this....as it will run in the early morning.