0

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?

0

2 Answers 2

1

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
1
  • Thanks this works perfectly, had to still use a Table Variable to insert into (To check if anything was actually selected) but this definitely sped up the query.
    – Daine
    Jun 26, 2018 at 23:36
0

Your script can be corrected like this,

DECLARE @UStartDate, @UEndDate DATETIME

SELECT TOP 1 @UStartDate=StartDate,@UEndDate =EndDate  FROM #Temp WHERE ID = @RequestID

IF (General.IsRangeBetweenDateRange@UStartDate, @UEndDate , @StartDate, @EndDate) = 1)

Otherwise there is no need of RBAR and two UDF.But we need more info to fix it.

3
  • Its not so much the flow of the script. It was that #Temp can't be used in a UDF context as its a temporary table. I guess the question is more, what alternative is there that would allow me to use a function to return a TINYINT. I have been looking at just using a table variable.
    – Daine
    Jun 26, 2018 at 6:27
  • @Daine , your script is not according to title of this post. Yes without getting must deeper you can say that table variable or CTE are alternative. But if you go deeper then there are lot of scope in improving existing script.
    – KumarHarsh
    Jun 26, 2018 at 7:01
  • it seems I am mistaken. Just for later knowledge if I provide the script of the function with the problem (In this case its using a Temporary Table and I was looking for a possible alternative) how is it not according to the title which asks for an alternative to a Temporary Table when inside of a UDF?
    – Daine
    Jun 26, 2018 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.