Create function with conditional calculation

Question

I tried to create a function with a conditional calculation before the RETURN statement, but SSMS states that there is an incorrect syntax near 'BEGIN IF @param2 IS NULL' and I can't figure out what is wrong with this code:

CREATE FUNCTION ConditionalFunction
(
    @input1 VARCHAR(20),
    @input2 VARCHAR(20)
)
RETURNS TABLE
AS BEGIN
    IF @input2 IS NULL
    BEGIN
        SET @input2 = field
        FROM table1
        WHERE filter = @input1
    END

    RETURN
    (
        SELECT field1, field2
        FROM table2
        WHERE filter1 = @input1
        AND filter2 = @input2
    )
END

My goal is to be able to call it either

ConditionalFunction('Foo',NULL)

or

ConditionalFunction('Foo','Bar')

In a way that the first call will make it detect a default value for @input2 based on @input1.

I wrote this code based on this answer.
This is SQL Server 13. Could you please tell me if there is anything I can do to make it compile?

N.B: I obviously replaced real names with generics, but the code structure remains the same

score 2 · Accepted Answer · answered Oct 17 '18 at 08:20

Try this, but change the @Data table types to your data types:

CREATE FUNCTION ConditionalFunction
(
    @input1 VARCHAR(20),
    @input2 VARCHAR(20)
)
RETURNS @Data TABLE
(
    field1 VARCHAR(12)
   ,field2 VARCHAR(12)
)
AS 
BEGIN

    IF @input2 IS NULL
    BEGIN
        SET @input2 = field
        FROM table1
        WHERE filter = @input1
    END

    INSERT INTO @Data
    SELECT field1, field2
    FROM table2
    WHERE filter1 = @input1
    AND filter2 = @input2


    RETURN;
END

There are several types of functions in SQL:

scalar return only one value
inline - returns a table (it is like view with parameters)
table-valued - returns a table

Your syntax looks like you need a inline function, but should contain only one SELECT statement returning the data. So, you need to create a table-valued function.

Create function with conditional calculation

1 Answers1