Can I avoid temp tables, etc.

[betbubble]

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_IDENTIFI ER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transact ion_Test]') and OBJECTPROPERTY( id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transact ion_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transact ion_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFI ER 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_IDENTIFI ER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transact ion_Test]') and OBJECTPROPERTY( id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transact ion_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transact ion_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_IDENTIFI ER 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?

[Erland Sommarskog]

(be*******@gmai l.com) writes:

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?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @@spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

What are you using SET ANSI_NULLS OFF for?

[Erland Sommarskog]

Alexander Kuznetsov (AK************ @hotmail.COM) writes:

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFI ERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[betbubble]

Erland and Alexander,
Thanks a lot. I will experiment with them and report back.

[betbubble]

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFI ERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.

[Erland Sommarskog]

(be*******@gmai l.com) writes:

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFI ERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx