Cursor and update

Question

Post reply

Cursor and update

phineas629

SSC-Addicted

Points: 426
More actions
January 24, 2014 at 12:31 am

#284781

Before Script Runs
Seller_No|Product_No|Product_Status
99999 |100000 |Availalble |
88888 |100000 |Null |
Expected Results
Seller_No|Product_No|Product_Status
99999 |100000 |Removal |
88888 |100000 |Available |
I have the following script. I am required to use the cursor. This will always be a small batch so no worries on overhead for the system.
The scenario is that we changed to a new seller. I want to mark the new seller as 'available' while changing the old seller to 'removal'.
Is it possible to do all this inside the cursor? The following is the script so far. I've been using the null value for searching the correct rows.
USE [OutletRetail]
GO
/****** Object: StoredProcedure [Outlet].[sp_UpdateProductStatus] Script Date: 01/16/2014 19:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
--Updates Product status codes to Available if NULL
ALTER PROCEDURE [Outlet].[sp_UpdateProductStatus]
AS
DECLARE @strProductNo varchar(20)
DECLARE @strSellerNo1 varchar(10)
DECLARE @strSellerNo2 varchar(10)
DECLARE UpdateProductCursor CURSOR FOR
SELECT Product_No, Seller_No
FROM Outlet.tblProductMaster
WHERE Product_Status IS NULL
OPEN UpdateProductCursor
FETCH NEXT FROM UpdateProductCursor INTO @strProduct_No, @strSellerNo1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSellerNo2 = NULL
SELECT @strSellerNo2 = Seller_No
FROM Outlet.tblProductMaster
WHERE Product_No = @strProductNo
AND Seller_No <> @strSellerNo1
AND Product_Status = 'Available'
IF (@strSellerNo2 IS NULL)
BEGIN
UPDATE Outlet.tblProductMaster
SET Product_Status = 'Available'
WHERE Product_No = @strProductNo
AND Seller_No = @strSellerNo1
END

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

phineas629 (1/24/2014)
... I am required to use the cursor...

Perfectly reasonable so long as this is homework. If not, then do it properly:

UPDATE Outlet.tblProductMaster SET

Product_Status = CASE

WHEN Seller_No = @strSellerNo1 AND Product_Status = 'Available' THEN 'Removal'

WHEN Seller_No = @strSellerNo2 AND Product_Status IS NULL THEN 'Available'

ELSE 'UNKNOWN' END

WHERE Product_No = @strProductNo

AND Seller_No IN (@strSellerNo1,@strSellerNo2)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DaveDB SSCrazy Points: 2173 More actions · Answer 2

I completely disagree with the use of cursors. They are heavy and slow and anything that you can do in a cursor you can be accomplished in a loop and/or temp table. Also, look into Derived tables and Correlated Sub Queries. Both will help you avoid cursors in the future and are much, much faster.

Just as a side note.... I have been on several interviews where I am asked how I feel about cursors. The correct answer to this question is that they are very slow and should only be used as a last resort. Similar to Triggers, you would only use a trigger where necessary.

Hope this helps.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

ChrisM@Work (1/24/2014)
phineas629 (1/24/2014)
... I am required to use the cursor...
Perfectly reasonable so long as this is homework.

It shouldn't be homework as I recognize this from a previous thread http://www.sqlservercentral.com/Forums/Topic1532233-338-1.aspx

You should try to get rid of the cursor. Even if this will be used only for a small set of rows, you might need to work with larger sets in the future and cursors will become a performance issue.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

DaveDB (1/24/2014)
I completely disagree with the use of cursors. They are heavy and slow and anything that you can do in a cursor you can be accomplished in a loop and/or temp table.

Dave,

Changing a cursor to a loop, won't be any better because a cursor is basically a loop.

Cursor should be used carefully and as last resort as you say. For administrative jobs they're a great tool.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

DaveDB (1/24/2014)
...and anything that you can do in a cursor you can be accomplished in a loop and/or temp table.

A temp table and While Loop is just as bad as a cursor and a nicely written "FireHose" cursor is just as easy to use. The key is to avoid the loop. Replacing a cursor with a Temp Table and While Loop is a futile effort and mostly a waste of time and effort.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

phineas629 SSC-Addicted Points: 426 More actions · Answer 6

I wanted to thank you guys for the suggestions. It's been a while since I've logged on. In the end I using a cursor as we only use a standard short list that would need to be parsed. This will not change for the life of the database.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

If it works for you, then is fine. If you want to continue to work with SQL, you should start trying to do it "the right way" because this DB might not grow but you might and will need to work with larger databases where performance will be an issue.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

phineas629 SSC-Addicted Points: 426 More actions · Answer 8

Thanks Luis,

I feel like I've been stuck in sql purgatory, just barely knowing enough to get by. I'm really hoping to make the leap to a real developer. I welcome any recommendations.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

phineas629 (2/12/2014)
I wanted to thank you guys for the suggestions. It's been a while since I've logged on. In the end I using a cursor as we only use a standard short list that would need to be parsed. This will not change for the life of the database.

Heh... when someone practices the piano and unless they're practicing to be a piano wielding comedian, do they intentionally hit the wrong notes just because no one is listening? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

phineas629 (2/12/2014)
Thanks Luis,
I feel like I've been stuck in sql purgatory, just barely knowing enough to get by. I'm really hoping to make the leap to a real developer. I welcome any recommendations.

Any code you write is a recommendation from you to the next developer to work on the same system. Do the best you can in the time you've been given.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden