Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cursor and update Expand / Collapse
Author
Message
Posted Friday, January 24, 2014 12:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:06 AM
Points: 14, Visits: 123
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


Post #1534361
Posted Friday, January 24, 2014 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1534398
Posted Friday, January 24, 2014 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, 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.
Post #1534526
Posted Friday, January 24, 2014 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 4,046, Visits: 9,200
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1534527
Posted Friday, January 24, 2014 8:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 4,046, Visits: 9,200
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1534529
Posted Friday, January 24, 2014 2:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534660
Posted Wednesday, February 12, 2014 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:06 AM
Points: 14, Visits: 123
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.
Post #1540577
Posted Wednesday, February 12, 2014 9:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 4,046, Visits: 9,200
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540852
Posted Wednesday, February 12, 2014 11:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:06 AM
Points: 14, Visits: 123
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.
Post #1540928
Posted Wednesday, February 12, 2014 12:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1540948
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse