Professional Web Applications Themes

stored procs and temp tables - Microsoft SQL / MS SQL Server

In an effort to facilitate code reuse, I have created a stored proc that creates a temp table. According to SQL Server doentation, this table "is deleted when you disconnect." My experience is that a temp table is deleted BEFORE I disconnect. I cannot even reference one immediately after its creation. See the following example. Any ideas? ------------------------------------------------------------ CREATE PROCEDURE zJimkSPTestLevel2 AS CREATE TABLE #jimk_sp_test (test_column char(30)) ------------------------------------------------------------ CREATE PROCEDURE zJimkSPTestLevel1 AS EXECUTE zJimkSPTestLevel2 INSERT INTO #jimk_sp_test SELECT 'Level 1 insert' SELECT * FROM #jimk_sp_test ------------------------------------------------------------ execute zJimkSPTestLevel1 result: Invalid object name '#jimk_sp_test' ------------------------------------------------------------...

  1. #1

    Default stored procs and temp tables

    In an effort to facilitate code reuse, I have created a stored proc
    that creates a temp table. According to SQL Server doentation,
    this table "is deleted when you disconnect."

    My experience is that a temp table is deleted BEFORE I disconnect. I
    cannot even reference one immediately after its creation.

    See the following example. Any ideas?

    ------------------------------------------------------------
    CREATE PROCEDURE zJimkSPTestLevel2 AS


    CREATE TABLE #jimk_sp_test
    (test_column char(30))

    ------------------------------------------------------------
    CREATE PROCEDURE zJimkSPTestLevel1 AS


    EXECUTE zJimkSPTestLevel2

    INSERT INTO #jimk_sp_test
    SELECT 'Level 1 insert'

    SELECT * FROM #jimk_sp_test

    ------------------------------------------------------------
    execute zJimkSPTestLevel1

    result: Invalid object name '#jimk_sp_test'

    ------------------------------------------------------------
    Jim Guest

  2. #2

    Default Re: stored procs and temp tables

    Hi Jim,

    Inside zJimkSPTestLevel1, once zJimkSPTestLevel2 completes (i.e. the table
    is created) , it's discarded and cleaned up.

    Instead try calling zJimkSPTestLevel1 as:

    CREATE PROCEDURE zJimkSPTestLevel1 AS 


    "Jim Kelleher" <org> wrote in message
    news:google.com... 


    oliver Guest

  3. #3

    Default Re: stored procs and temp tables

    From the SQL 2000 Books Online:

    <Excerpt href="tsqlref.chm::/ts_create2_8g9x.htm">

    Temporary tables are automatically dropped when they go out of scope,
    unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped
    automatically when the stored procedure completes. The table can be
    referenced by any nested stored procedures executed by the stored
    procedure that created the table. The table cannot be referenced by the
    process which called the stored procedure that created the table.

    All other local temporary tables are dropped automatically at the end of
    the current session.

    </Excerpt>

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Jim Kelleher" <org> wrote in message
    news:google.com... 


    Dan Guest

  4. #4

    Default Re: stored procs and temp tables

    Thanks for the reply!

    Oy vey. I made the mistake of following the help text!

    Maybe another approach would be in order. My goal is the simple desire
    to CENTRALIZE the definition of a table for subsequent, repeated
    "instantiations."

    This is a common business programming requirement satisfied in COBOL by
    copymembers and in C by #define.

    To my disapointment, I'm finding SQL Server is not up to the challenge
    of serious business programming. Or am I wrong?

    Any ideas?





    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Jim Guest

  5. #5

    Default Re: stored procs and temp tables

    Why not have a permanent table and work with a column which identifies each user/connection.
    Or create the temp table before entering the first proc.
    Or use a ##global temp table.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Jim Kelleher" <org> wrote in message
    news:phx.gbl... 


    Tibor Guest

Similar Threads

  1. Stored Procs
    By @ HOTMAIL DOT COM in forum ASP Database
    Replies: 6
    Last Post: November 26th, 12:06 PM
  2. SQL stored procs in 8.1
    By Murty in forum IBM DB2
    Replies: 4
    Last Post: August 25th, 11:21 PM
  3. Stored Outlines For Queries In PL/SQL Stored Procs
    By Salaam Yitbarek in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139