|
|
Posted by Dan Guzman on 11/07/07 13:58
If the units requested (input
> parameter) for an item, does not exist in TableA,
> a new record is then inserted into TableA for this new item. The update
> statement should be able to update the
> quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit
> under Item_ID IT1003.
I didn't have time to analyze the insert requirement. Below is an example
of an alternative approach you might consider.
CREATE PROCEDURE dbo.up_parmins_item
@Date_DT VARCHAR(22),
@Item_No INT,
@Qty_IT INT,
@Unit_Price_IT INT
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE
@Error int,
@Expected_Rowcount int
SELECT
@Error = 0
DECLARE @TableB TABLE
(
Dates datetime,
Item_ID char(6) NOT NULL,
Item_No int NOT NULL,
Original_Qty int NOT NULL,
Qty int NOT NULL,
Unit_Price money NOT NULL
PRIMARY KEY (Item_ID, Dates)
)
INSERT INTO @TableB
SELECT
TableA.Dates,
TableA.Item_ID,
TableA.Item_No,
Qty AS Original_Qty,
CASE
WHEN previous_inventory.begin_inventory + Qty <= @Qty_IT THEN Qty
ELSE previous_inventory.begin_inventory + Qty - @Qty_IT
END AS Qty,
TableA.Unit_Price
FROM dbo.TableA
JOIN (SELECT
Dates,
Item_No,
COALESCE((SELECT SUM(Qty)
FROM dbo.TableA AS available_stock
WHERE
available_stock.Item_No = TableA.Item_No
AND available_stock.Dates < TableA.Dates
), 0) AS begin_inventory
FROM dbo.TableA
WHERE
Item_No = @Item_No
) AS previous_inventory ON
TableA.Item_No = previous_inventory.Item_No
AND TableA.Dates = previous_inventory.Dates
WHERE
previous_inventory.begin_inventory < @Qty_IT
SELECT
@Expected_Rowcount = @@ROWCOUNT
BEGIN TRAN
UPDATE a
SET
Qty = b.Qty
FROM dbo.TableA AS a
JOIN @TableB AS b ON
b.Item_ID = a.Item_ID AND
b.Original_Qty = a.Qty
IF @@ROWCOUNT <> @Expected_Rowcount
BEGIN
RAISERROR('Inventory changed by another user. No changes were made.', 16,
1)
SET @error = 1
GOTO Done
END
INSERT INTO dbo.TableB
SELECT
Dates,
Item_ID,
Item_No,
Qty,
Unit_Price
FROM @TableB
COMMIT TRAN
Done:
IF @error <> 0
BEGIN
ROLLBACK
END
RETURN @@ERROR
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave dcartford" <dcartford@gmail.com> wrote in message
news:4731a323$0$10303$815e3792@news.qwest.net...
>
> I need to create a stored procedure with a parameterised input. An input
> parameter is declared as a variable,this will hold the units that a user
> wants to insert from TableA into TableB. Using a CASE clause, within a
> stored procedure, it will first need to compare the input parameter
> against the avaiable units in TableA, let's say
> in this case, an input of 3 units of Item_No (1235), is required to be
> inserted into TableB's 4 columns (refer below for field names). The
> stored procedure should be able to assign the units required base on the
> earliest Item_ID (1st Primary key comes first, on a first-in-first-out
> basis) from TableA, by first filtering a number of sequential criteria.
> First it would search for the Item_No (1235) in TableA, next its
> available units,if both condition are met, next, insert a row of record
> into TableB's 4 columns, If units required is insufficient,
> it would loop for the next available units, then inserting the next row
> of records into TableB. An update procedure
> is also required to be created to update the units being used for the
> assignment in TableA (refer table below
> for updated records for Item_No (1235).
>
> The following are the fieldnames in TableA running on SQL Server 2000:
>
> Dates as datetime
> Item_ID as int (Primary key)
> Item_No as int
> Qty as int
> Unit_Price as int
>
> TableA consist of the following info :
> ====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> ====================================================
> 30/10/2007 | IT1000 | 1234 | 2 | 4
> ====================================================
> 30/10/2007 | IT1001 | 1235 | 2 | 6
> ====================================================
> 28/09/2007 | IT1002 | 1236 | 4 | 8
> ====================================================
> 01/11/2007 | IT1003 | 1235 | 2 | 2
>
> INSERT Result in TableB after executing the stored procedure :
> ====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> ====================================================
> 30/10/2007 | IT1001 | 1235 | 2 | 6
> ====================================================
> 01/11/2007 | IT1003 | 1235 | 1 | 2
>
> UPDATE Result in TableA after executing the stored procedure :
> =====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> =====================================================
> 30/10/2007 | IT1000 | 1234 | 2 | 4
> =====================================================
> 30/10/2007 | IT1001 | 1235 | 0 | 6
> =====================================================
> 28/09/2007 | IT1002 | 1236 | 4 | 8
> =====================================================
> 01/11/2007 | IT1003 | 1235 | 1 | 2
>
> The following stored procedure was created:
>
> Create procedure dbo.up_parmins_item
> @Date_DT VARCHAR(22),
> @Item_No INT,
> @Qty_IT INT,
> @Unit_Price_IT INT as
>
> Declare @Item_ID INT
>
> Select @Item_ID = Item_ID
> From TableA
> Where Item_No = @Item_No and Qty <> 0
> -- If item not exist, then insert into TableA
> If @Item_ID IS NULL
> BEGIN
> Insert into TableA
> ( Dates,Item_No,Qty,Unit_Price)
> Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)
> Set @Item_ID = @@IDENTITY
> END
> -- If item exist, and Qty is not 0, then insert into TableB and Update
> TableA
> If @Item_ID IS NOT NULL and Qty <> 0
> BEGIN
> Set @Item_ID = Min(Item_ID),
> Insert into TableB
> ( Dates ,Item_ID,Qty,Unit_Price)
> Values(Getdate(),@Item_ID,@Qty_IT,@Unit_Price_IT)
> -- update quantity balance for Item_No (1235) in TableA
> Update TableA
> Set Qty = (@Qty_IT - Qty)
> Where Item_No = @Item_No
> END
>
> If @@Error > 0
> BEGIN
> RAISERROR ('Update and Insert Items failed',16,1)
> RETURN 99
> END
>
> RETURN 0
>
> My issue here is, how do I create a CASE clause or if-else statement to
> verify the quantity (Qty) available in
> TableA is sufficient for the input parameter (@Qty_IT) to be inserted
> into TableB? The procedure should be able to perform a loop in the CASE
> or If-else statement to allocate the quantity requested base on the
> earliest
> Item_ID existing in TableA, and the next available units in the
> subsequent Item_ID (e.g. (1st) IT1001 - 2 units,
> (2nd) IT1003 - balance of 1 unit) If the units requested (input
> parameter) for an item, does not exist in TableA,
> a new record is then inserted into TableA for this new item. The update
> statement should be able to update the
> quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit
> under Item_ID IT1003.
>
> Any suggestion to the above stored procedure would be helpful, Thanks
> guys!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
[Back to original message]
|