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

Bobby P 231 Reputation points
2024-11-07T13:49:08.8166667+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,213 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,960 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,670 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Naveen Kumar M 75 Reputation points
    2024-12-24T03:34:42.0033333+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.