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 ***

If the units requested (input
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 (AT) gmail (DOT) com> wrote

Try to get some tips on accounting database design at:
(http://www.accountingdatabasedesign.blogspot.com/)



*** Sent via Developersdex http://www.developersdex.com ***