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.
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.
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.
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.
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.
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?
ALTER PROCEDURE test1
/* SET NOCOUNT ON */
select dbo.function2(5) as studentID
CREATE TABLE ##temp_table (session_id int);
INSERT INTO ##temp_table VALUES(@session_id);
Stored Procedure 2:
ALTER PROCEDURE test2
@sid int output
/*EXECUTE test1 1;*/
SELECT @sid = session_id
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