locked
Loop through user defined table conditionally RRS feed

  • Question

  • User-257070954 posted

    Hi All
    I am writng proceduere for grocery application
    If one person order two times in same day i want to update his firs order
    In my Procedure i am passing one userid as integer and table variable for product details
    in his second order, if order same product i want to update first order Qty + Sceond order Qty
    If new product i want to inset into table

    odeerdate i am setting as default as getdate

    Below giving my procedure

    CREATE PROCEDURE [dbo].[AddOrder]
    @UserId INT,
    @tblOrderItems OrderType READONLY,
    @OrderId VARCHAR(50)
    AS
    BEGIN

    DECLARE @NumberofOrderType int, @RowCount int
    SET @NumberofOrderType = (SELECT count(*)FROM @tblOrderItems)

    WHILE @RowCount <= @NumberofOrderType
    BEGIN
    -- If ordered already in first order i want to Update here else insert How to check that
    INSERT INTO tblOrder(UserId, ItemId, Qty)
    SELECT @UserId, ItemIdFROM @tblOrderItems
    SET @RowCount = @RowCount + 1
    END

    END

    Thursday, March 26, 2020 4:45 AM

Answers

  • User-1330468790 posted

    Hi binustrat,

     

    It seems like the SP would be a complex since you said you want to update the date with a condition fulfilled and insert the data else.

    You can refer to below SP:

    CREATE PROCEDURE [dbo].[AddOrder]
    @UserId INT,
    @tblOrderItems OrderType READONLY,
    @OrderId VARCHAR(50)
    AS
    -- BEGIN SP
    BEGIN
    
    DECLARE @NumberofOrderType int, @RowCount int, @Qty int, @AddQty int, @ItemId int
    DECLARE @OrderTypeWithRowNumber Table (rowNum INT, ItemId INT, Qty INT)
    DECLARE @today date
    
    INSERT INTO @OrderTypeWithRowNumber (rowNum,ItemId, Qty)
    SELECT ROW_NUMBER() OVER(ORDER by ItemId) rowNum, * FROM @tblOrderItems
    
    SET @NumberofOrderType = (SELECT count(*) FROM @tblOrderItems)
    SET @RowCount = 1
    SET @today = CONVERT(date, GETDATE())
    
    WHILE @RowCount <= @NumberofOrderType
    -- BEGIN WHILE
    BEGIN
    
    SELECT @ItemId = ItemId, @AddQty = Qty From @OrderTypeWithRowNumber WHERE rowNum = @RowCount
    SELECT @Qty = Qty FROM tblOrder Where UserId = @UserId AND ItemId = @ItemId AND OrderDate = @today
    
    print @ItemId
    print @AddQty
    
    IF @Qty is not null
    -- BEGIN IF
    BEGIN
    -- Update
    update tblOrder 
    SET Qty = @Qty + @AddQty WHERE UserId = @UserId AND ItemId = @ItemId AND OrderDate = @today
    print 'update'
    -- END IF
    END
    ELSE
    -- BEGIN ELSE
    BEGIN
    -- Insert
    INSERT INTO tblOrder VALUES (@UserId, @ItemId, @AddQty, CONVERT(date, GETDATE()))
    print 'insert'
    -- END ELSE
    END
    
    
    
    SET @RowCount = @RowCount + 1
    
    -- END WHILE
    END
    
    
    -- END SP
    END

    I defined OrderType as below since you treat the UserId as an input parameter :

    CREATE TYPE StateTbl AS TABLE
    ( ItemId INT, 
      Qty INT
    );

     Execute the SP:

    USE [xxx.MDF]
    GO
    
    DECLARE	@return_value Int,
    		@tblOrderItems ordertype
    
    		INSERT INTO @tblOrderItems (ItemId, Qty) VALUES (2,1)
    		INSERT INTO @tblOrderItems (ItemId, Qty) VALUES (1,3)
    	
    
    EXEC	@return_value = [dbo].[AddOrder]
    		@UserId = 1,
    		@tblOrderItems = @tblOrderItems,
    		@OrderId = NULL
    
    SELECT	@return_value as 'Return Value'
    
    GO
    

    DataBase Before Execution:

    DataBase After Execution:

    PS: I can not find the meaning of the "OrderId" here from your description but I still leave it in the SP.

       

    Moreover, I suggest you don't separate the insert operation into update and insert parts.

    Then you can retrieve the data in a detailed level (datetime, Order Id, etc.)

    If you need to sum the Qty for one specific user in a specific day, what you need to do is just fetching data by a simple sum operation from the database.

      

    Hope this can help you.

    Best regards,

    Sean

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 26, 2020 8:53 AM