Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
fdtoo
Starting Member
28 Posts |
Posted - 2007-11-07 : 06:45:05
|
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 sayin 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 belowfor updated records for Item_No (1235).The following are the fieldnames in TableA running on SQL Server 2000:Dates as datetimeItem_ID as int (Primary key)Item_No as intQty as intUnit_Price as intTableA 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 | 2INSERT 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 | 2UPDATE 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 | 2The 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 asDeclare @Item_ID INTSelect @Item_ID = Item_IDFrom TableAWhere Item_No = @Item_No and Qty <> 0 -- If item not exist, then insert into TableAIf @Item_ID IS NULLBEGINInsert into TableA ( Dates,Item_No,Qty,Unit_Price) Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)Set @Item_ID = @@IDENTITYEND-- If item exist, and Qty is not 0, then insert into TableB and Update TableAIf @Item_ID IS NOT NULL and Qty <> 0BEGINSet @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 TableAUpdate TableA Set Qty = (@Qty_IT - Qty) Where Item_No = @Item_NoENDIf @@Error > 0 BEGIN RAISERROR ('Update and Insert Items failed',16,1) RETURN 99ENDRETURN 0My 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 earliestItem_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! |
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 10:45:04
|
great job including the details and examples, but just to clarify you are looking for alternative suggestions for the stored procedure to do something like the following...if EXISTS(SELECT 0 FROM TableA WHERE Item_No = @Item_No AND Qty > @Qty_IT)begin--BEGIN TRAN? INSERT TableB... UPDATE TableA SET Qty = Qty - @Qty_IT WHERE Item_No = @Item_No--COMMIT ROLLBACK?endelsebegin INSERT TableA...end |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-07 : 11:01:45
|
[code]UPDATE tASET tA.Qty = tA.Qty - tB.QtyFROM TableA AS tAINNER JOIN TableB AS tB ON tB.Dates = tA.DatesWHERE tB.Item_ID = tA.Item_ID AND tB.Item_No = tA.Item_No[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|