Focuses on SQL Server, especially T-SQL development and programming, with the aim of presenting projects and problems in SQL Server and the thought process behind delivering a solution.
Sunday, July 11, 2010
Recursive Stored Procedures and Temp Tables
If a stored procedure creates a temp table and then calls itself recursively, will the newly instantiated stored procedure have access to the temp table?
I am a database developer focusing on ETL/BI development. My technological background centers on Microsoft SQL Server technologies such as T-SQL programming, SSIS, and SSRS, but also includes Informatica, Visual Foxpro, and application development such as Visual Basic and Java.
My webpage is http://www.jessemclain.com/, and I can be reached via email at jesse@jessemclain.com.
Doesn't look like it:
ReplyDeleteCREATE PROCEDURE spr_test
@iteration INT
AS
IF @iteration>10
RETURN
CREATE TABLE #test(field1 INT)
INSERT INTO #test
SELECT @iteration
SET @iteration=@iteration+1
EXEC spr_test @iteration
SELECT * FROM #test
DROP TABLE #test
GO
EXEC spr_test 1