SQL Server – How to Get Around Not Being Able to Use Temp Tables in Functions

functionssql servertemporary-tables

So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates.

Seems simple, have a table-valued function to get all staff if that position then use a single-valued function that runs through that staff members leave or current shifts to see if any will stop them from working this shift.

ALTER FUNCTION [Human_Resources].[fIsEmployeeOffshoreBetweenDates] (@EmpID INT, @StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
BEGIN    
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    /*Then it exists*/
    DROP TABLE #Temp

    SELECT *
    INTO #Temp
    FROM Human_Resources.v_GetEmployeeManning(@EmpID)

    DECLARE @RequestID INT

    WHILE EXISTS(SELECT * FROM #Temp)
    BEGIN

        SELECT TOP 1 @RequestID = ID FROM #Temp

        IF (General.IsRangeBetweenDateRange((SELECT TOP 1 StartDate FROM #Temp WHERE ID = @RequestID), (SELECT TOP 1 EndDate FROM #Temp WHERE ID = @RequestID), @StartDate, @EndDate) = 1)
        BEGIN
            PRINT N'IS WITHIN RANGE'
            RETURN 0;
        END

        DELETE #Temp WHERE ID = @RequestID

    END

    RETURN 1;
END

But then I found out that Functions can't actually contain temporary tables. Does anyone know how I could get around this?

Best Answer

Your best bet is to rewrite the function to remove the need for the temp table at all. This will benefit you in other ways as your current row by row approach will not scale well.

The example below shows one way of getting the same results without using a temp table. I had to make assumptions about the data types since you didn't include the function header. 

DECLARE @EmpID INT
DECLARE @StartDate DATE
DECLARE @EndDate DATE

;WITH CTE_HR
    (
    SELECT HRM.* 
        , IsRangeBetween = General.IsRangeBetweenDateRange(HRM.StartDate, HRM.EndDate, @StartDate, @EndDate)
    FROM Human_Resources.v_GetemployeeManning(@EmpID) AS HRM
    )
SELECT * FROM CTE_HR
WHERE IsRangeBetween = 1
Related Question