SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor and update


Cursor and update

Author
Message
phineas629
phineas629
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 146
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



ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42234 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
DaveDB
DaveDB
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 215
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
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42632 Visits: 19847
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
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42632 Visits: 19847
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
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218785 Visits: 41998
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
phineas629
phineas629
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 146
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
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42632 Visits: 19847
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
phineas629
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 146
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
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218785 Visits: 41998
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search