Using Temporary Tables and Re-Using Temporary Tables in a SSRS Report

Question

So we need to standardize Member Eligibility by using a SQL Server Stored Procedure that will be called, Executed by our Patient/Member SSRS Reports.

The SQL Server Stored Procedure currently uses a Global Temporary Table to pass its result set back to the calling Report's SQL Server Stored Procedure.

Just trying to think the best way to go about doing this as multiple reports may be running or kicked off at the same time to utilize this standardized executed SQL Stored Procedure.

Maybe a Staging Table is a better idea but we will need someway to discern my result set, Business User #1, from say Business User's #2 result set. Even if it is in a Staging Table, I guess we'd have to incorporate cleaning up that Staging Table upon the final report result set presentation SELECT.

Why we were thinking more of a Global ##Temporary Table because that would purge itself after execution. But then we have the dilemma of how we'd name that Global ##Temporary Table to make it unique from one report execution to the next and across reports.

Just wondering if anyone has faced this dilemma before and logistics of dealing with Temporary Tables and uniquely defining and using a Temporary Table across SSRS Reports so if re-using a standardized SQL Server Stored Procedure my result set is unique and different from say Business User #2.

Any insight and help would be GREATLY appreciated.

Thanks!

Answer 1

Hi @Bobby P

This is a common challenge when dealing with shared resources like stored procedures, temporary tables, and SSRS reports in multi-user environments.

Challenges:

1. Concurrency Issues:
   • Multiple users or reports running at the same time might overwrite or conflict with each other's data when using shared resources (e.g., a global temporary table).
2. Uniqueness:
   • Each report's execution needs a unique identifier to separate its data from others.
3. Cleanup:
   • If using staging tables, stale or unused data can accumulate and impact performance unless cleaned properly.
4. Global Temporary Tables:
   • These are automatically dropped when the session ends, but naming conflicts arise if multiple sessions/reports access the same stored procedure simultaneously.

My suggestion would be "use a Staging Table with Unique Identifiers":

Instead of a global temporary table, use a staging table that persists data for the duration of the report execution. Here's how:

• Add a column for a unique session identifier (e.g., SessionID or ExecutionID) in the staging table.
• Generate a unique SessionID for each report execution (e.g., using NEWID() or the SSRS Globals!ExecutionID property).
• Pass the SessionID to the stored procedure and include it in every record inserted into the staging table.
• Modify the stored procedure to filter data based on the SessionID when returning results.
• Clean up the staging table after the report completes using a scheduled cleanup job or a final cleanup query in SSRS.

Example:

CREATE TABLE MemberEligibilityStaging (
    SessionID UNIQUEIDENTIFIER,
    MemberID INT,
    EligibilityStatus VARCHAR(50),
    ValidUntil DATETIME,
    PRIMARY KEY (SessionID, MemberID)
);

-- Stored Procedure Example
CREATE PROCEDURE GetMemberEligibility
    @SessionID UNIQUEIDENTIFIER
AS
BEGIN
    -- Insert result set into staging table with the provided SessionID
    INSERT INTO MemberEligibilityStaging (SessionID, MemberID, EligibilityStatus, ValidUntil)
    SELECT 
        @SessionID, 
        MemberID, 
        EligibilityStatus, 
        ValidUntil
    FROM 
        SomeSourceTable;

    -- Return results for the current session
    SELECT 
        MemberID, 
        EligibilityStatus, 
        ValidUntil
    FROM 
        MemberEligibilityStaging
    WHERE 
        SessionID = @SessionID;
END;

Pros:

• Data is explicitly tied to each execution instance.
• Avoids naming conflicts with global temporary tables.
• Staging tables are easier to debug.

Cons:

• Requires cleanup (can be automated with a SQL Agent job or triggers).

Please let me know if any questions

Kindly accept answer if it helps

Thanks

Naveen