how to make this stored procedure more performant

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