Best way of creating dynamic conditions in sql functions

Question

I have a challenge regarding some "too much code produced" or not most elegant coding way issue:

ALTER FUNCTION [dbo].fn_Function
    (@Location NVARCHAR(30) ='ALL')
RETURNS @ReportTable TABLE(DateOfProviValidity date,
                           DateOfFixumValidity date,
                           UserID NVARCHAR(50)
                          )
AS
BEGIN
    DECLARE @CurrentMonth DATE

    SET @CurrentMonth = dbo.fn_getFirstOfMonth(getdate())

    IF @Location IS NOT NULL AND @Location <> 'ALL'
        INSERT INTO @ReportTable
            SELECT * 
            FROM dbo.fn_getFinalPData(@CurrentMonth) 
            WHERE 1 AND Location = @Location
    ELSE
        INSERT INTO @ReportTable
            SELECT * 
            FROM dbo.fn_getFinalPData(@CurrentMonth) 
            WHERE 1 AND Location IS NOT NULL

    RETURN
END

Is there a possibility to get rid of the initial IF @Location statement and add it more closely to the Where clause?

My issue is that the case statement doesn't work as I can't do a Location equals or IS NOT NULL statement.

Answer 1

Try this one,

SELECT * 
FROM dbo.fn_getFinalPData(@CurrentMonth) 
WHERE   1 
    AND Location    =   ISNULL(NULLIF(@Location,'ALL'),Location)

Answer 2

What about something like this?

INSERT INTO @ReportTable
SELECT * 
FROM dbo.fn_getFinalPData(@CurrentMonth) 
WHERE ISNULL(@Location, 'ALL') <> 'ALL'
OR Location = @Location