CODEWITHSUNDEEP
sqlsql-servert-sql
David Perry
David Perry

Reputation: 28

Update Loop in Stored Procedure

I'm currently creating an order database system. My SQL Server is pretty limited so this may have a simple answer I haven't noticed. Is there a way to do :

while($row = mysqli_fetch_array($result))

in SQL Server. I'm trying to update stock when an order is confirmed.

This is what I have so far and as you can see what it is I want in the comments. Well it's what the PHP part of my brain would do.

--UPDATE ORDER -- 

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME ='CONFIRMED_ORDER')
   DROP PROCEDURE CONFIRMED_ORDER
GO

CREATE PROCEDURE CONFIRMED_ORDER
   @ORDERID [INT],
   @PRODUCTID [INT],
   @ORDERSTATUS [NCHAR](10),
   @STOCK [INT],
   @QUANTITY[INT],
   @CURRENTSTOCK[INT]
AS
BEGIN
   BEGIN TRY
      UPDATE ORDERS
      SET ORDERSTATUS = @ORDERSTATUS 
      WHERE ORDERID = @ORDERID;

      IF (@ORDERSTATUS = 'Confirmed')
      BEGIN
          SET @PRODUCTID = (SELECT PRODUCTCODE 
                            FROM ORDERLINES 
                            WHERE ORDERID = @ORDERID);

          /*WHILE (@PRODUCTID)
          SET @QUANTITY = SELECT QUANTITY 
                          FROM ORDERLINES 
                          WHERE ORDERID = @ORDERID AND PRODUCTCODE = @PRODUCTID;

          SET @CURRENTSTOCK = SELECT UNITSINSTOCK 
                              FROM PRODUCTS 
                              WHERE PRODUCTCODE = @PRODUCTID;

          UPDATE PRODUCTS
          SET UNITSINSTOCK = @CURRENTSTOCK - QUANTITY;*/
    END
 END TRY
 BEGIN CATCH
    SELECT ERROR_NUMBER() AS ERRORNO, ERROR_MESSAGE() AS ERRORMSG
    ROLLBACK
END CATCH
END
GO

As I said, I'm fairly limited with SQL so any answer, even if it's to say this isn't possible would be much appreciated.

Thanks in advance.

Upvotes: 0

Views: 3242

Answers (2)

Christian Phillips
Christian Phillips

Reputation: 18769

You can use this inside the IF block

UPDATE PRODUCTS
SET UNITSINSTOCK = UNITSINSTOCK - ISNULL(@QUANTITY,0)
WHERE PRODUCTCODE = @PRODUCTID;

As for the LOOP, you should probably create this client side...

while($row = mysqli_fetch_array($result))
 //call sp

Upvotes: 1

Luaan
Luaan

Reputation: 63742

As I said in the comment, you don't need a cycle at all. You just have to think natively in SQL, rather than trying to write the same thing as in PHP:

update [Products]
 set [UnitsInStock] = [UnitsInStock] - OL.[Quantity]
from [Products] P
inner join [OrderLines] OL on OL.[ProductCode] = P.[ProductCode]
where OL.[OrderId] = @OrderId

This will update all the "stocks" of all the products in the order in one statement.

Note that this assumes there's only one order line for each product ID - if you don't ensure there aren't duplicities, you need to change this a bit to sum all the lines of a single product ID together.

Oh, and you're mixing procedure parameters with local variables. Declare the parameters the way you do, but then do the local variable declaration after the as, e.g.:

declare 
        @STOCK [INT],
        @QUANTITY[INT],
        @CURRENTSTOCK[INT];

Upvotes: 5

Related Questions