CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Alfred
Alfred

Reputation: 255

View the result set of a temporary table's data using SELECT within the stored procedure that creates the same temporary table?

I have to create a stored procedure that adds a new pay period(adds 7 days) to a temporary table named #TempPayPeriod. This tables has all of the pay period data from the PayPeriod table. So I create a procedure that takes this permanent data move all of it into this temporary table and add a new pay period to that temporary table.

Which I did now I need to view that temporary table so I can compare it with this data and make sure it matches up

enter image description here

In my code I write SELECT * FROM #TempPayPeriod right after I execute the procedure. I get a Command run successfully but no result set to look at?

USE PR
GO

CREATE PROC spAddPay
            @StartDate smalldatetime = NULL,
            @EndDate smalldatetime = NULL
AS
DROP TABLE IF EXISTS #TempPayPeriod;

SELECT * INTO #TempPayPeriod FROM PayPeriod;

SET @StartDate = (SELECT MAX(PerFrom) FROM #TempPayPeriod);
SET @StartDate = DATEADD(day, 7, @StartDate);
SET @EndDate = (SELECT MAX(PerThru) FROM #TempPayPeriod);
SET @EndDate = DATEADD(day, 7, @EndDate);

BEGIN TRY
    INSERT #TempPayPeriod
    VALUES (@StartDate, @EndDate);
END TRY
BEGIN CATCH
    PRINT 'Record was not added';
    PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
END CATCH

EXEC spAddPay;
SELECT * FROM #TempPayPeriod;
GO

Upvotes: 0

Views: 43

Answers (1)

Aaron Hughes
Aaron Hughes

Reputation: 476

Your temporary table loses scope once the stored procedure completes. You would need to move the select statement inside of the stored procedure or make the temporary table global by using the double pound sign notation. ##TempPayPeriod

OPTION 1 - Return the result from within the stored procedure

USE PR
GO

CREATE PROC spAddPay
            @StartDate smalldatetime = NULL,
            @EndDate smalldatetime = NULL
AS
BEGIN
    DROP TABLE IF EXISTS #TempPayPeriod;

    SELECT * INTO #TempPayPeriod FROM PayPeriod;

    SET @StartDate = (SELECT MAX(PerFrom) FROM #TempPayPeriod);
    SET @StartDate = DATEADD(day, 7, @StartDate);
    SET @EndDate = (SELECT MAX(PerThru) FROM #TempPayPeriod);
    SET @EndDate = DATEADD(day, 7, @EndDate);

    BEGIN TRY
        INSERT #TempPayPeriod
        VALUES (@StartDate, @EndDate);
    END TRY
    BEGIN CATCH
        PRINT 'Record was not added';
        PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
    END CATCH

    SELECT * FROM #TempPayPeriod
END

GO

EXEC spAddPay;

GO

OPTION 2 - Use Global Temp Table

USE PR
GO

CREATE PROC spAddPay
            @StartDate smalldatetime = NULL,
            @EndDate smalldatetime = NULL
AS
BEGIN
DROP TABLE IF EXISTS ##TempPayPeriod;

SELECT * INTO ##TempPayPeriod FROM PayPeriod;

SET @StartDate = (SELECT MAX(PerFrom) FROM ##TempPayPeriod);
SET @StartDate = DATEADD(day, 7, @StartDate);
SET @EndDate = (SELECT MAX(PerThru) FROM ##TempPayPeriod);
SET @EndDate = DATEADD(day, 7, @EndDate);

BEGIN TRY
    INSERT ##TempPayPeriod
    VALUES (@StartDate, @EndDate);
END TRY
BEGIN CATCH
    PRINT 'Record was not added';
    PRINT 'Error: ' + CONVERT(varchar, ERROR_NUMBER(),1) + ': ' + CONVERT(varchar, ERROR_MESSAGE(),1);
END CATCH
END
EXEC spAddPay;
SELECT * FROM ##TempPayPeriod;
GO

Upvotes: 1

Related Questions