Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Unanswered: Temporary Table in 3 diffirent Stored Proc

    I know there maybe something similar of what im asking for but i just cant find it.

    I have 3 Stored procedure.

    SPA - create a temporary table "sp_getListOfChildren"
    SPB - insert the data into the temp table "sp_InsertCategoriesFound"
    SPC - display the list of categories i found "sp_ListingAvailableCategories"

    process:
    SPA call SPB and SPC call SPA

    my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..

    Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"

    ============MY "SPC" CODE=============
    alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier
    AS

    exec sp_getListOfChildren @CurrentCategoryID

    select * from #TblTempCat

    select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc00 AS Create TABLE ##myTemp99(Col1 int)
    Go
    CREATE PROC mySproc01 AS INSERT INTO ##myTemp99(Col1) SELECT 1
    GO
    CREATE PROC mySproc02 AS SELECT Col1 AS Yup_Its_Still_There FROM ##myTemp99
    GO
    CREATE PROC mySproc03 AS DROP TABLE ##myTemp99
    GO
    
    CREATE PROC mySproc99
    AS
    
    EXEC mySproc00
    EXEC mySproc01
    EXEC mySproc02
    EXEC mySproc03
    GO
    
    EXEC mySproc99
    GO
    
    DROP PROC mySproc00
    DROP PROC mySproc01
    DROP PROC mySproc02
    DROP PROC mySproc03
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    # is a local temporary table
    ## is a global temporary table

    as stated in the first reply the ## should hook you up.

  4. #4
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    Note however, that SQL Server Books on Line says
    "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 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 that called the stored procedure that created the table."

    But how do you create the nested SP that uses that Temp table?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Joeller View Post
    But how do you create the nested SP that uses that Temp table?
    I'd do something like:
    Code:
    CREATE PROCEDURE C
    AS
    BEGIN
       SELECT a.bar, b.bar, a.bar * b.bar
          FROM #foo AS a
    	  CROSS JOIN #foo AS b
    END
    GO
    CREATE PROCEDURE B
    AS
    BEGIN
       INSERT INTO #foo (bar)
          VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
    END
    GO
    CREATE PROCEDURE A
    AS
    BEGIN
       CREATE TABLE #foo (
          bar  INT
       )
    
       EXECUTE B  --  Can access #foo because it's called within A
       EXECUTE C  --  Can access #foo because it's called within A
    END
    GO
    EXECUTE A
    GO
    DROP PROCEDURE C
    DROP PROCEDURE B
    DROP PROCEDURE A
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Mag tape ADP lives again!

    I have 3 Stored procedures.
    We do not use “sp_” in T_SQL procedure names; It has special meaning to the one-pass compiler. Programmers who leaned to program on these old systems often make a design error called “tibling” where they put meta data into data element names, like prefixing table names with “tbl-”

    We do not use temp tables if we can avoid it. This is how non-REBMS tape processing file systems worked in the 1950's. Mount a scratch tape; write to this scratch tape; dismount and pass the scratch tape to the next procedural step.

    An SQL programmer would write a query inside whatever he was trying to do.

    Since you were rude and did not post any DDL or DML, we cannot help you.

    From the vague narrative, you do not understand data modeling or RDBMS. There is no such crap as a “category_id”; an attribute can be a “<something in particular>_category” or a “<something in particular>_id”, but never, never that mix of what ISO-11179 calls attribute proprieties.

    SPA - create a temporary table "sp_getListOfChildren"
    SPB - insert the data into the temp table "sp_InsertCategoriesFound"
    SPC - display the list of categories I found "sp_ListingAvailableCategories"
    The word “process” is evil in a declarative language like SQL. We declare instead. The word “list” is absurd in SQL; we get sets, not lists as results! The word “children” is scary in SQL; is comes from network databases, not RDBMS.

    process:
    SPA call SPB and SPC call SPA
    An endless cycle might be possible! But we have no code.

    my problem is in the SPC. it seems that the table does not exist anymore when I do a select but in the message tab of my SQL analyzer I can see that the table have some data before executing that store proc..
    Of course not! Unlike the magnetic tapes in your mindset which have a physical existence, temp table exist in a scope of declaration and disappear when you leave it.

    What little you did post is a nightmare. It looks like you are using GUIDs for your category encoding! That is truly awful and I would fire you for it. Have you ever been to library? Do you know the Dewey Decimal Classification system? Categories are known in advance, not discovered on the fly; their encoding is organized and not random.

    Nothing you posted makes sense; would you like to try again and get it right?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, I should have pointed out that there are now better answers than temp tables for most problems that would have used temp tables in older versions of SQL Server.

    Quote Originally Posted by Celko View Post
    We do not use “sp_” in T_SQL procedure names; It has special meaning to the one-pass compiler. Programmers who leaned to program on these old systems often make a design error called “tibling” where they put meta data into data element names, like prefixing table names with “tbl-”
    I understand that Hungarian Notation is one of your pet peeves and I agree with you (I don't like it either) but a lot of people still use it and worse yet a lot of people still TEACH it. While your objection is academically sound, the way you present it puts people off so badly that very few of them ever hear your message. Logic and formal proofs are important and you and I have had some great (and some heated) discussions about both of them, but people are far more important than pure academics. Your schtick is great if you want to become another FP, but I see you as both far more knowledgeable and much better able to reach out to people than him.

    Quote Originally Posted by Celko View Post
    We do not use temp tables if we can avoid it. This is how non-REBMS tape processing file systems worked in the 1950's. Mount a scratch tape; write to this scratch tape; dismount and pass the scratch tape to the next procedural step.

    An SQL programmer would write a query inside whatever he was trying to do.
    Agreed, but there can often be mitigating circumstances. I've recently run across a number of "engine bugs" in different SQL engines, and due to time pressure the only workable solution was to use a temp table or customize the engine's source code. Sometimes we have to resort to "old school" ways to work around real world problems!

    Quote Originally Posted by Celko View Post
    Since you were rude and did not post any DDL or DML, we cannot help you.

    From the vague narrative, you do not understand data modeling or RDBMS. There is no such crap as a “category_id”; an attribute can be a “<something in particular>_category” or a “<something in particular>_id”, but never, never that mix of what ISO-11179 calls attribute proprieties.

    The word “process” is evil in a declarative language like SQL. We declare instead. The word “list” is absurd in SQL; we get sets, not lists as results! The word “children” is scary in SQL; is comes from network databases, not RDBMS.

    << Editorial snip >>

    What little you did post is a nightmare. It looks like you are using GUIDs for your category encoding! That is truly awful and I would fire you for it. Have you ever been to library? Do you know the Dewey Decimal Classification system? Categories are known in advance, not discovered on the fly; their encoding is organized and not random.

    Nothing you posted makes sense; would you like to try again and get it right?
    You did notice that post is over a decade old, right? You're correct, but I'm betting that the OP won't notice.

    As an aside, in relational databases categories are known in advance. The whole point of HADOOP and "big data" is that they are meant to wade into the morass and find the appropriate structure and categories. While I would have agreed with you wholeheartedly a decade ago, I've moved on to the point where I would only agree in terms of relational databases... The DBA now needs to be able to cope with data that has no structure (at least when they first encounter it) and the need to find/impose that structure.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Nothing you posted makes sense; would you like to try again and get it right?
    Nothing you posted was useful or relevant; would you like to try again and be helpful?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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