|
|
Posted by Dave dcartford on 11/07/07 11:36
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 ***
Navigation:
[Reply to this message]
|