Post reply

temp table

  • January 23, 2008 at 11:43 am

    #185843

    Hello,

    We have some stored procedures and I would like to integrate them together. Some of the SPs use temp table and need to access these temp table from the main SP.

    I know I can use global temp table to do that. but then I have to create a unique name every time the SP is called. This will force me to write a lot of code to generate dymanic SQL which I try to avoid.

    Is there another way to achieve my goal? Is there a way to get a set of data by calling a SP and store this data set to a temp table....

    thanks alot

  • January 23, 2008 at 11:59 am

    #771012

    If you create a temp table in a procedure, then any procedures you call from that one will be able to see the temp table. If you create a temp table in a child proc, a proc that called it won't be able to see the temp table

    so.

    Create PROC SomeStoredProc AS

    CREATE TABLE #Test2 (

    ID int

    )

    SELECT * from #Test -- will work.

    GO

    CREATE PROC TestingTempTables AS

    CREATE TABLE #Test (

    ID int

    )

    EXEC SomeStoredProc

    SELECT * from #Test2 -- will fail with object not found

    GO

    Hence, create the temp table at the highest level of the procs, and all below will be able to see.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2008 at 3:00 pm

    #771080

    SS2008 allows you to pass in a table parameter.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply