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?
at

1 comment:

  1. UnknownFebruary 18, 2012 at 5:27 AM

    Doesn't look like it:

    CREATE 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

    ReplyDelete