news:1158267377.424643.236290@p79g2000cwp.googlegroups.com...
>I need help on two questions:
> 1. Is temp table the only way to pass recordsets from a nested stored
> procedure to a calling stored procedure? Can we avoid temp tables in
> this case?
> 2. Are operations in a stored procedure treated as a transaction?
>
> Any help will be greatly appreciated.
>
> Background: We need to use temp table to pass recordsets from a nested
> stored procedure to a calling stored procedure. Our understanding is
> that in this case, we have no choice but to use temp tables. So, we
> need to optimize the performance as much as possible. To do this, we
> wanted to find out whether operations in a stored procedure are treated
> as a transaction. We are using SQL 2000 SP4. I could not find any
> answers so I did the following experiment.
>
> Experiment 1:
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[Wiz_SP_Transaction_Test]
> GO
>
> CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
> AS
>
> Update
>        Articles
> SET
>        IsUpdate = 20
> where
>        ArticlesId < 80000
>
> SELECT * from Articles
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "SELECT * from Articles" takes a long time (about 40 seconds) to
> complete
>
> Before executing the SP, the IsUpdate attribute for all articles is 30.
> Then I executed this SP.  Before the SP is finished, I end the SP
> manually. I checked the IsUpdate attribute again, and found that all
> Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
> operations did not rollback. I interpret this to mean that the whole SP
> is not treated as a transaction.
>
> Then, I did experiment 2 below. This time, I explicitly declared the
> transaction.
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[Wiz_SP_Transaction_Test]
> GO
>
> CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
> AS
> BEGIN TRANSACTION
> Update
>        Articles
> SET
>        IsUpdate = 50
> where
>        ArticlesId < 80000
>
> SELECT * from Articles
>
> IF @@ERROR <> 0 ROLLBACK TRANSACTION
> COMMIT TRANSACTION
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Before this second SP, the IsUpdate attribute is 20 (set in the first
> experiment). I run this second SP and ended it manually before it
> finished. I checked the IsUpdate attributes for all Articles's
> (ArticlesId < 80000), but their Isupdate attribute is 50. So the
> operation did not rollback either. But we have declared the transaction
> explicitly. Does this mean that the SP is still not treated as a
> transaction?
>