Cursor alternative

Does anybody have a way to re-write this without using a cursor? This takes a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.What you have to do is look at the functionality of each of the procs that
you exec. By the looks of it, you're doing work for just one employee at a
time in those procs. You need to fuse that functionality with the SELECT on
which you declared your cursor. You can do an UPDATE with a JOIN, for
example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
Does anybody have a way to re-write this without using a cursor? This takes
a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.|||Rewrite each of the sprocs to do their work using set-based logic rather
than processing one employee at a time. Since you call each of them with a
cursor, I bet you prolly have some cursors inside the sprocs too. Classic
mistake when developing database applications. You are not alone! :-)
The problem is that shifting your mindset to set-based from row-based can be
a VERY difficult undertaking. The two are not even in the same solar
system.
Read some beginning Transact SQL books. Hire a consultant/mentor to fix
your stuff and learn from him/her at the same time.
--
TheSQLGuru
President
Indicium Resources, Inc.
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
> Does anybody have a way to re-write this without using a cursor? This
> takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
>|||On Aug 31, 7:32 pm, morphius <morph...@.discussions.microsoft.com>
wrote:
> Does anybody have a way to re-write this without using a cursor? This takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
create procedure calculate as
begin
exec usp_calculate_retirement
exec usp_calculate_benefits
exec usp_calculate_vacation
exec usp_calculate_bonuses
exec usp_calculate_promotion
end
for each process you now have to handle all employees.