Results 1 to 8 of 8

Thread: TABLE variable

  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: TABLE variable

    I have 2 stored procedures in which I want to transfer values between each other. Can i use TABLE variable to achieve that? suppose I create a TABLE variable, does that mean the temporary table is stored somewhere on the server. Then after running the first stored proc and when I run the 2nd proc, will I be able to access this TABLE variable from 2nd proc? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not unless you are using 2008 and passing the table around as a parameter. Assuming this isn't what you mean (that you actually want a table that is scoped to the session) then you'll need to use a temporary table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Thanks. Yes am using VWD 2008. That is what I need, ie, i don't want to pass the table everywhere as a parameter, i want the temporary table to be stored somewhere on the server (maybe its default location where temp db are stored on sql server) after i set the value in it so that any time i run another stored proc independently, I can access the table. Is that possible? Btw, i want to store the newly inserted row id in the table so that other procedures may use it.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you want the data in this table available "any time i run another stored proc independently" then why put it in a temporary structure? It sounds like this should just go in permanent table. The only reason really you would use a temporary table is to ensure it is scope to the session you called it in.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    i dont think the permanent table will do in this case because the newly inserted row id is used by the 2nd procedure for an entry to a table's row. After that I dont need this variable you see.
    - My first procedure enters details about students personal info into a table
    - 2nd procedure enters details about finance in another table
    - so newly inserted student row ID is a foreign key to the finance table that is why I need to pass it temporarily for this session.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you have your first procedure return a dataset (consider the OUTPUT option of your INSERT/UPDATE) and then store the information in the application session?

    Alternatively, why not do all the work in one sproc?
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    Thanks georgev. Yes was thinking of using 1 sproc but i didnt like the idea of 1 single big block of code you see. I wanted to break it down into smaller sections so that debugging can be made easily. The first solution you mentioned, do you mean that I return a dataset to vb.net and then I use vb code to retrieve the output values? in either way how do I achieve that?

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    I have created the 2 example sprocs as thus:

    Stored Procedure 1:

    Code:
    	
    	ALTER PROCEDURE test1
        @session_id int
        
    AS
    BEGIN
    /* SET NOCOUNT ON */
    	select dbo.function2(5) as studentID
       CREATE TABLE ##temp_table (session_id int);
       INSERT INTO ##temp_table VALUES(@session_id);
    END
    	
    	RETURN
    Stored Procedure 2:
    Code:
    ALTER PROCEDURE test2
      @sid int output
    AS
    BEGIN
       /*EXECUTE test1 1;*/
       SELECT @sid = session_id
              
              FROM ##temp_table
    
      SELECT @sid
    END
    Am I getting it right here? If I run the first proc, the temp table is created. This is stored in the tempdb of sql server. Now, if I run test2, I should be able to retrieve the value stored in that table from test1, but am getting error 'Invalid object name '##temp_table'. Any help? Thanks

Posting Permissions

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