Passing a Temp Table to a Stored Procedure
With SQL 2008, table variables can now be passed into stored
procedures.
Temp tables can also be passed into stored procedures from
other stored
procedures and triggers.
This is true in 2008 and earlier
versions like SQL 2005 and 2000.
This allows for cleaner code as many
lined stored procedures can now be broken
up into multiple
sub-procedures.
A child stored procedure can see its parent's
temp table.
This child procedure can INSERT, DELETE and UPDATE rows
just like
a normal temp table.
When the child returns to the parent,
the data is changed and can be passed
into more child stored procedures.
Using
a temp table and passing it to another procedure, in most cases, will
be
faster than looping through a result set with a cursor and calling a
stored procedure with parameters.
An example:
CREATE PROC dbo.udfParent
AS
BEGIN
SELECT 1 AS MyInt,
'Hello' AS MyChar
INTO #myTemp
EXEC dbo.udfChild
SELECT *
FROM #myTemp
DROP TABLE #myTemp
RETURN (0)
END
GO
CREATE PROC dbo.udfChild
AS
BEGIN
SELECT *
FROM #myTemp
UPDATE #myTemp
SET MyInt = 2
RETURN (0)
END
GO
EXEC dbo.udfParent
(Fonte http://mysqlserverblog.com/2009/07/05/passing-a-temp-table-to-a-stored-proceedure.aspx)
Ciao a Tutti 