CODEWITHSUNDEEP
sql-servert-sqlstored-procedurestransactionstemp-tables
Jared Benedict
Jared Benedict

Reputation: 13

Accessing temp table created within a stored procedure outside of procedure but within same transaction

I have a transaction that calls a stored procedure which creates a temp table. I need to be able to access this temp table outside of the stored procedure after it has been ran. Note: for what I am trying to do, I cannot use global temp tables.

Example:

Here is an example of the stored procedure:

CREATE PROCEDURE [dbo].[GetChangeID]()
AS
BEGIN
    IF OBJECT_ID('tempdb..#CurrentChangeID') IS NOT NULL
        DROP TABLE #CurrentChangeID

    SELECT '00000000-0000-0000-0000-000000000000' AS ChangeID INTO @CurrentChangeID

END
GO

Here is an example of the transaction:

BEGIN TRANSACTION
DECLARE @changeID uniqueidentifier
EXEC dbo.GetChangeID

DECLARE @test uniqueidentifier
SET @test = (SELECT ChangeID FROM #CurrentChangeID)

COMMIT TRANSACTION
GO

The issue is that it cannot find a table named #CurrentChangeID.

How can I make it to where it can see this table without declaring it as a global temp table such as ##CurrentChangeID?

------UPDATE------

So let me give more context to my question because that was just a simplified example. So what I am ultimately trying to do is this: 1. Begin Transaction 2. Call stored procedure that generates the GUID 3. Then update row in a given view that has a trigger. 4. Within that trigger get the GUID that was generated within the sp. 5. Commit.

Upvotes: 0

Views: 3156

Answers (2)

Jared Benedict
Jared Benedict

Reputation: 13

Thank you lad2025 and Dan Guzman for your input. The way I was originally trying to do this was definitely incorrect.

I did, however, figure out a way to accomplish this task.

Modified Stored Procedure:

CREATE PROCEDURE [dbo].[GetChangeID]()
AS
BEGIN
    DECLARE @ChangeID uniqueidentifier
    ...
    Code that generates the uniqueidentifier, @ChangeID.
    ...

    --This can be seen within the context of this batch.
    SET CONTEXT_INFO @ChangeID
END
GO

Then anywhere within this transaction that you would like to access the changeID, you just have to use the following query:

SELECT CONTEXT_INFO as changeID
FROM sys.dm_exec_requests
WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

First of all you can't get access to local temp table defined in SP outside stored procedure. It will always be out of scope.

Second you probalbly don't even need temp table. In your example:

SET @test = (SELECT ChangeID FROM #CurrentChangeID)

it looks like you want only one value.

I propose to use output parameter.

CREATE PROCEDURE [dbo].[GetChangeID](
      @test UNIQUEIDENTIFIER OUTPUT
)
AS
BEGIN
     -- ...

    SET @test =  '00000000-0000-0000-0000-000000000000';

END;

And call:

DECLARE @changeID uniqueidentifier
EXEC dbo.GetChangeID @chaneId OUTPUT;

SELECT @changeId;

Upvotes: 2

Related Questions