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

how to locate the exact user name who owns a db object

hi all,
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:

>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards