how to access session temporary table from different stored proced

how to access session temporary table from different stored proced

Post by THVjYXMgS2 » Thu, 23 Jul 2009 23:23:01



I believe the reason for this is because the local temporary table only live
for the duration of the stored procedure. So in your example, the temporary
table is created on test1 stored procedure and then got deleted after the
test1 stored procedure has completed. This article should explain more:
http://www.yqcomputer.com/

Alternatively, have you consider using function, so to re-write what you have:

CREATE FUNCTION test1 (
@session_id int,
@currentTime bigint)
RETURNS @temp_table TABLE (session_id int, cur_time bigint)
AS
BEGIN
INSERT INTO @temp_table VALUES(@session_id, @currentTime)
RETURN
END
GO

CREATE PROCEDURE test2 (
@sid int output,
@ct bigint output)
AS
BEGIN
SELECT @sid = session_id,
@ct = cur_time
FROM test1 (1,2)
END
GO

DECLARE @a INT
DECLARE @b BIGINT
EXEC test2 @a OUTPUT, @b OUTPUT
SELECT @a, @b

Hope that's helpful