Any other way instead of using Cursor loop? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Any other way instead of using Cursor loop?

Author

Topic

DestinyJack
Starting Member

22 Posts

Posted - 2005-09-13 : 06:46:31

If i am going to loop through every record in a table with about 40k recordsand use the data to update another table, how should I loop it? I am currently using cursor to loop but it makes the performance very very poor. About 10 minutes everytime I try to run this stored procedure. Can anyone give me some idea on how to improve the following stored procedure?

CREATE PROCEDURE [dbo].[pc_Update_PartnerData]
@USERID VarChar(200),
@TableName VarChar(200)

AS
DECLARE
@sTable VarChar(50),
@sTable1 VarChar(50),
@sSQL VarChar(8000),
@Partner_id Integer,
@FileName nVarChar(10),
@Sales Numeric(18),
@Price Numeric(18),
@Remarks nVarChar(500),
@ShopID nVarChar(500)

SET @sTable = @TableName

SET @sSQL = 'DECLARE TMP_WORK CURSOR GLOBAL'
SET @sSQL = @sSQL + ' FOR SELECT Partner_id, Filename, ISNULL(Sales, 0) AS Sales, ISNULL(Price, 0) As Price, ISNULL(Remarks, ''''), ShopID'
SET @sSQL = @sSQL + ' FROM ' + @stable
SET @sSQL = @sSQL + ' WHERE Partner_id IS NOT NULL'
SET @sSQL = @sSQL + ' AND Filename IS NOT NULL'
SET @sSQL = @sSQL + ' ORDER BY FileName, Partner_id'
EXEC (@sSQL)

OPEN TMP_WORK

FETCH NEXT
FROM TMP_WORK
INTO @Partner_id, @Filename, @Sales, @Price, @Remarks, @ShopID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sTable1 = 'PART_' + LEFT(@Filename,2)

SET @sSQL = 'UPDATE ' + @sTable1 + ' SET SSales = ' + convert(varchar(30),@Sales) + ' , SPrice = ' + convert(varchar(30),@Price)

SET @sSQL = @sSQL + ' ,Remarks = ' + '''' + @Remarks + ''''
SET @sSQL = @sSQL + ' ,SRetID = ' + '''' + RIGHT(@ShopID, 4) + ''''
SET @sSQL = @sSQL + ' WHERE ID = ' + Convert(Varchar(30),@Partner_id)

Exec(@sSQL)

FETCH NEXT FROM TMP_WORK into @Partner_id, @Filename, @Sales, @Price, @Remarks, @ShopID
END

CLOSE TMP_WORK
DEALLOCATE TMP_WORK

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 07:16:03

Explain us on what you are trying to do

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-13 : 07:23:58

Why don't you use normal update with join? Syntax:

update a
  set a.col1 = <expression 1>, ...
  from tableA a
  join tableB b on <expression>
...

DestinyJack
Starting Member

22 Posts

Posted - 2005-09-13 : 20:43:01

Thanks a lot, exactly what I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources