Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    25

    Unanswered: What's up with this SP?

    I am trying to execute this stored procedure in Query Analyzer. Before I threw in a WHILE loop, it would execute the query batch forever with no results displaying or rows affected. Now that I have the while statement in there, I click to execute and "The command(s) completed sucessfully" appears. However, the rows of my table are not changed. What's going on here?




    CREATE PROCEDURE [UpdateShipmentsWithTerritory]

    AS
    SET NOCOUNT ON

    DECLARE @RowCnt INT

    SET @RowCnt = @@ROWCOUNT
    SET ROWCOUNT 20000

    WHILE @RowCnt > 0
    BEGIN
    UPDATE Shipments
    SET TerritoryID = ISNULL((SELECT TerritoryID FROM vwAccountTransfers

    WHERE Shipments.AccountID = vwAccountTransfers.AccountID
    AND vwAccountTransfers.EffectiveDate =

    (SELECT MIN(EffectiveDate)
    FROM vwAccountTransfers

    WHERE Shipments.AccountID = vwAccountTransfers.AccountID
    AND Shipments.DateInvoice < vwAccountTransfers.EffectiveDate)),

    (SELECT TerritoryID FROM vwAccountTransfers
    WHERE Shipments.AccountID = vwAccountTransfers.AccountID
    AND vwAccountTransfers.EffectiveDate Is Null ))

    SET @RowCnt = @@ROWCOUNT

    WAITFOR DELAY '00:00:30'

    END

    SET ROWCOUNT 0
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are setting @RowCnt to the value of @@ROWCOUNT at the start of your procedure, and who knows what value that is? Likely zero, os when you test to see if @RowCnt > 0 you get FALSE as a result and your loop never runs.

    Since you are only running a single statement in your loop, you don't need to store @@ROWCOUNT in a variable. You don't even need BEGIN/END clauses:

    CREATE PROCEDURE [UpdateShipmentsWithTerritory]

    AS
    SET NOCOUNT ON

    SET ROWCOUNT 20000

    select 1 --Guarantees @@ROWCOUNT > 0 to start.

    WHILE @@ROWCOUNT > 0
    [UPDATE SHIPMENTS statement...]

    WAITFOR DELAY '00:00:30'

    SET ROWCOUNT 0
    GO

    Your inner query could use some work, though. Avoid using subquerys in your WHERE clause and recast the statement as a join to a subquery.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, I agree on reworking the update, but I think BEGIN...END are still needed because otherwise the loop will not have a DELAY and continue firing on each iteration. Now before entering into the loop I'd just SET @RowCnt=1, and at the end of the loop right after UPDATE go ahead and SET @RowCnt=@@ROWCOUNT. This way you'll stay in the loop as you intend to.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I didn't notice the DELAY was inside the loop.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •