Cursor and update

  • 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

  • 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

  • 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.

  • 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
  • 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
  • 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)

  • 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.

  • 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
  • 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.

  • 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)

  • 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

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

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