Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor

Author

Topic

SQL360
Starting Member

20 Posts

Posted - 2008-09-17 : 16:01:59

Can you suggest me to write the below SP without using the cursor?

I am trying to avoid tempdb grow out of whack due to cursors being used
On the store proc in which there are large transactions/batches being processed.

--Desc: Load Data for each equipment assigned to the Demo Company

CREATE PROCEDURE sp_DemoLoadDailyProfile AS

declare @EquipmentID as int
declare @CompanyID as int
declare @cmd as varchar(8000)

set @CompanyID = 778
DECLARE profile_cursor CURSOR FORWARD_ONLY KEYSET FOR
select EquipmentID from Equipment where CompanyID = @CompanyID

OPEN profile_cursor
FETCH NEXT FROM profile_cursor INTO @EquipmentID

WHILE @@fetch_status = 0
BEGIN
exec sp_DemoPollOnDemand @CompanyID, @EquipmentID
FETCH NEXT FROM profile_cursor INTO @EquipmentID
End
CLOSE profile_cursor
DEALLOCATE profile_cursor
GO

SQL360

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-17 : 16:03:30

In order to write this set-based, we would need to see the code in sp_DemoPollOnDemand.

By the way, you should not prefix your stored procedures with sp_ for performance reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQL360
Starting Member

20 Posts

Posted - 2008-09-18 : 01:22:29

Ok, Thank you.

SQL360

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources