SQL UDF - loop through a table-CodePudding

I'm developing a UDF in SQL where by applying the function to a date column, it could generate which dates are holidays or not. It will be comparing to a View (dbo.Holidays) that I have already set up that contains all the public holidays that need to be compared with.

The code I have so far:

CREATE FUNCTION [dbo].[IsHoliday] (
            @Date DATE) 

RETURNS BIT

AS BEGIN
DECLARE @Check BIT

SELECT @Check = CASE WHEN @Date = [Holiday] 
                    THEN 1
                    ELSE 0
                    END
                FROM dbo.Holidays

        RETURN (@Check)
END

Sample data (Holidays table):

Intended results:

2022-12-12 |    0 |
2020-01-01 |    1 |
2020-01-02 |    1 |
2020-02-06 |    1 |

Currently, the results show up as:

2022-12-12 |    0 |
2020-01-01 |    1 |
2020-01-02 |    0 |
2020-02-06 |    0 |

The issue I'm having so far - when I apply this function to a date column, it only generates '1' to the first public holiday and all the rest are generated as '0'. I feel like the View needs to be looped through each and every row of the dataset that I'm applying the function to. How can I fix it?

Thank you

Tried including a case based in join instead, but didn't work that either.

CodePudding user response：

You current query is just taking the top most (in undefined order) record. You need to check a specific record which you can do with EXISTS:

CREATE FUNCTION [dbo].[IsHoliday]
(
    @Date DATE
) 
RETURNS BIT
AS
BEGIN
    RETURN
        CASE WHEN EXISTS (
            SELECT 1
            FROM dbo.Holidays
            WHERE [Holiday] = @Date
        ) THEN 1 ELSE 0 END;
END;