locked
Loop through user defined table conditionally RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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