Use case or if in Table Value function to run query based on passed in value in SQL Server

Question

I'm trying to use a table-valued function (TVF) to execute one of 2 queries based on the value passed in. If the value passed in is NULL, I want to return all rows. If the value is specified, I want only those rows where the parameter equals the value specified.

CREATE FUNCTION [dbo].[search_test]
    (@param int)
RETURNS TABLE 
AS
    RETURN
        IF (@param = NULL)
            SELECT TOP 500 recordID 
            FROM jct_RecordParameter 
            ORDER BY recordID

        IF (@param >= 0)
            SELECT TOP 500 recordID 
            FROM jct_RecordParameter 
            WHERE parameterID = @param 
            ORDER BY recordID
END

I get an error:

Incorrect syntax near keyword 'if'

When I replace "RETURN" with "BEGIN," the syntax error shifts to "near 'BEGIN'".

Answer 1

You can't use an IF statement in a Table-valued function. But you can use an OR condition:

CREATE FUNCTION [dbo].[search_test] (@param int)
RETURNS TABLE
AS
RETURN (
    SELECT TOP 500 recordID
    FROM jct_RecordParameter
    WHERE (@param IS NULL) OR (parameterID = @param)
    ORDER BY recordID
)