|
|
Posted by betbubble on 09/14/06 20:55
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 are 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?
Navigation:
[Reply to this message]
|