Remove the cursor from this query

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

Transact-SQL (2000)

Remove the cursor from this query

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 18:47:55

I need to rewrite a query so that it doesn't use a cursor. All that it does is execute a stored procedure for each AssetID found in a particular record set. Given the below code, how can it be modified so that it doesn't use a cursor?

BTW, this code is just an example.


DECLARE @AssetID INT

DECLARE SomeCursor CURSOR FOR  
SELECT @AssetID = AssetID
FROM Table1
WHERE CompanyID = 1001

OPEN SomeCursor 

FETCH NEXT FROM SomeCursor 
INTO @AssetID

WHILE @@FETCH_STATUS = 0

BEGIN
	EXEC usp_Proc1 @AssetID

	FETCH NEXT FROM SomeCursor
	INTO @AssetID
END

CLOSE SomeCursor 
DEALLOCATE SomeCursor

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-27 : 18:57:55

well it won't give you much benefit but

DECLARE @AssetID INT, @maxAssetID int
select @AssetID = -1, @maxAssetID = max(AssetID)
FROM Table1
WHERE CompanyID = 1001

while @AssetID < @maxAssetID
begin
select @AssetID = min(AssetID) from Table1 WHERE CompanyID = 1001 and AssetID > AssetID
EXEC usp_Proc1 @AssetID
end

I've lost my title so guess I can be ambivalent about cursors now.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-27 : 19:12:17

Tara --

The key to losing the cursor isn't so much the code you have posted, but rather the stored procedure that is being called. To call a stored procedure 1 row at a time from a table, a cursor is the way to go. The best thing to do here, however, is to re-write the stored procedure so that it does whatever it does for ALL rows at once, instead of 1 at a time.

So, what does usp_Proc1 do ? Can you post the code for that?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 19:22:07

Jeff, thanks for the info.

quote:

So, what does usp_Proc1 do ? Can you post the code for that?

I was asked by a developer how to call a stored procedure for each AssetID returned in a record set. I do not even know which stored procedure that he wants to call for this. I have e-mailed him about it. Once I know which stored procedure it is, I'll clean it up some so that I can post it here.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 19:52:15

Apparently, the stored procedure is not done yet. I am going to work with the developer to see what can be done so that the stored procedure can handle multiple assets.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-27 : 20:06:20

You can put some assetts into a temp table and call the SP and it can join to the temp table.
In this way you can batch up the assetts so that it doesn't have to do too much in one batch but isn't limitted to one.
You can use the same technique as I posted before to get the batch.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-27 : 22:00:55

quote:

You can put some assetts into a temp table and call the SP and it can join to the temp table.

I think it's even easier: based on what you've posted, it seems like the stored procedure should just take an argument of @CompanyID and then do the work on all of that company's assets.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-28 : 13:03:29

I'm not sure if that is possible though. A lot of data has to be collected for the individual asset in order to determine if it needs to be updated. Hopefully though we'll be able to figure out how to do it at the company level. If I can't figure it out, I'll post sample code that will represent what the developer is trying to do.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 16:18:02

quote:

A lot of data has to be collected for the individual asset in order to determine if it needs to be updated.

Tara,

What do you mean by "Collected"

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-28 : 16:50:12

By collected, I mean selected out of different tables and put into variables so that the variables can be tested for certain conditions.

For instance, let's take this example:

DECLARE @var1 INT
DECLARE @var2 VARCHAR(50)
...

SELECT @var1 = t1.Column2, @var2 = t2.Column3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1 = t2.Column1
WHERE t1.Column3 = 1001

SELECT...

SELECT...

IF @var1 = 50
SET @var3 = 0
...

IF @var3 = 0
UPDATE Table3
SET Column5 = @var5
WHERE Column1 = @var6

Really lame example, but the point is that a bunch of things need to be evaluated to determine if the Asset needs to be updated.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 17:30:32

But I'm thinking that this could all be done with Joins and a (massive perhaps) CASE statement.

Can you post the sproc or is it propietary...if so maybe a snippet...

Just a thought.

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-28 : 18:39:53

I was thinking of joins and a case statement as well. I am not too sure what I can post out of the stored procedure. I'll see if a snippet can be posted (will need to wait til the developer is somewhat done with the stored procedure though).

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources