|
|
Posted by Erland Sommarskog on 09/14/06 21:21
(betbubble@gmail.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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|