ALTER PROCEDURE dbo.sp_AddProdutionPlanning
(
@.ProductionPlanning_Product uniqueidentifier,
@.ProductionPlanning_Date datetime,
@.ProductionPlanning_Quantity int
)
AS
Begin TRAN
Declare @.NewID uniqueidentifier;
Declare @.NewItemID uniqueidentifier;
Declare @.NewItemBatchID uniqueidentifier;
Declare @.Ingr uniqueidentifier;
Declare @.IngrQty int;
Declare @.Batch uniqueidentifier;
Declare @.BtchQty int;
Declare @.BtchConsumed int;
Declare @.MixQty int;
Declare @.QtyLeft int;
Set @.NewID= newid();
INSERT INTO ProductionPlanning
(ProductionPlanning_ID, ProductionPlanning_Product, ProductionPlanning_Date, ProductionPlanning_Quantity, ProductionPlanning_Created,
ProductionPlanning_PreviousUpdate, ProductionPlanning_LastUpdated, ProductionPlanning_Deleted)
VALUES (@.NewID,@.ProductionPlanning_Product,@.ProductionPlanning_Date,@.ProductionPlanning_Quantity,getdate(),getdate(),getdate(),0)
Select NULL mykey, ProductRecipe_ID, ProductRecipe_Ingredient, ProductRecipe_Quantity into #tblRecipe From ProductRecipe Where ((ProductRecipe_Product = @.ProductionPlanning_Product) And (ProductRecipe_Deleted = 0))
Set rowcount 1;
Update #tblRecipe Set mykey=1;
While @.@.Rowcount <>0
Begin
Set rowcount 1;
Select @.Ingr=ProductRecipe_Ingredient,@.IngrQty = ProductRecipe_Quantity From #tblRecipe;
Delete from #tblRecipe where mykey=1;
Set @.MixQty = (@.IngrQty * @.ProductionPlanning_Quantity);
Set @.NewItemID = newid();
INSERT INTO ProductionPlanningItem
(ProductionPlanningItem_ID, ProductionPlanningItem_Ingredient, ProductionPlanningItem_Planning, ProductionPlanningItem_Quantity)
VALUES (@.NewItemID,@.Ingr,@.NewID,@.MixQty)
Select NULL mykey,ProductIngredientBatch_ID, ProductIngredientBatch_Quantity, ProductIngredientBatch_Consumed Into #tblBatch From ProductIngredientBatch Where ProductIngredientBatch_Ingredient = @.Ingr And ProductIngredientBatch_Deleted = 0;
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Set rowcount 1;
Update #tblBatch set mykey=1;
While @.@.rowcount <>0
Begin
set rowcount 1;
Select @.Batch=ProductIngredientBatch_ID, @.BtchQty=ProductIngredientBatch_Quantity, @.BtchConsumed = ProductIngredientBatch_Consumed From #tblBatch
delete from #tblBatch where mykey=1
Set @.NewItemBatchID = newid();
Set @.QtyLeft = @.BtchQty - @.BtchConsumed;
if(@.QtyLeft > @.MixQty)
Begin
INSERT INTO ProductionPlanningItemBatch
(ProductionPlanningItemBatch_ID, ProductionPlanningItemBatch_Item, ProductionPlanningItemBatch_Batch, ProductionPlanningItemBatch_Quantity)
VALUES (@.NewItemBatchID,@.NewItemID,@.Batch,@.MixQty)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Update ProductIngredientBatch Set ProductIngredientBatch_Consumed = (@.BtchConsumed + @.MixQty) Where ProductIngredientBatch_ID = @.Batch
if (@.@.ERROR <> 0) Goto ERR_HANDLER
End
else
Begin
INSERT INTO ProductionPlanningItemBatch
(ProductionPlanningItemBatch_ID, ProductionPlanningItemBatch_Item, ProductionPlanningItemBatch_Batch, ProductionPlanningItemBatch_Quantity)
VALUES (@.NewItemBatchID,@.NewItemID,@.Batch,@.QtyLeft)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Update ProductIngredientBatch Set ProductIngredientBatch_Consumed = 0 Where ProductIngredientBatch_ID = @.Batch
if (@.@.ERROR <> 0) Goto ERR_HANDLER
End
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Print @.Batch;
UPDATE ProductIngredient
SET ProductIngredient_Consumed = ProductIngredient_Consumed + @.MixQty
WHERE (ProductIngredient_ID = @.Ingr)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
set rowcount 1;
update #tblBatch set mykey=1
End
Drop table #tblBatch;
Set rowcount 1;
update #tblRecipe set mykey=1
End
Commit Tran
RETURN 0
ERR_HANDLER:
Print 'An error occured';
ROLLBACK TRAN
RETURN 1
Given that nobody has taken a stab at this, I will give you some pointers. Yes, it is possible to rewrite the SP using set-based logic. Start with correlated subqueries that can maintain the running total. Using CTEs in SQL Server 2005 also is an option.
|||And perhaps you could use TRY / CATCH blocks to obtain a better error handling