SQL Server 05 - table valued function - execute different Selects based on the value of parameter passed?

Question

I would like to implement a solution where the Table-Valued-Function returns different set of data based on the value of the parameter passed to it.

For Example: something like this:

CREATE FUNCTION test(@param char(6))
RETURNS TABLE
AS
RETURN
(
IF @param IS NULL
    select * from this
ELSE
    select * from that
END
)

@Deepshikha It gives me the error: Invalid syntax near the keyword 'IF' and Invalid syntax near the keyword ')' — Ocelot, Oct 08 '14 at 05:51

score 0 · Answer 1 · answered Oct 08 '14 at 06:49

You can re-write the function as:

CREATE FUNCTION test(@param char(6))
RETURNS @retInformation TABLE
(
    -- Columns returned by the function
    ID int PRIMARY KEY NOT NULL  
)
AS
-- Returns the required Id if input parameter is null.
BEGIN    
    IF @param IS NULL
    BEGIN
        INSERT @retInformation 
        select ID from this
    END
    ELSE
    BEGIN
        INSERT @retInformation
        select ID from that
    END;
    RETURN;
END;

SQL Server 05 - table valued function - execute different Selects based on the value of parameter passed?

1 Answers1