Table-Valued User-Defined Function parameter used for IN clause

Question

How can I create a Table-Valued User-Defined Function in SQL Server that takes an input parameter for an IN clause?

I'm trying to write this very simple function:

CREATE FUNCTION dbo.MyTableValuedFunction (@myList VARCHAR(MAX))
RETURNS @myTable TABLE (
    [ID] INT,
    [Name] VARCHAR(MAX)
)
AS
BEGIN
    INSERT INTO @myTable
    SELECT ID, Name FROM MyTable WHERE ID IN (@myList)
    RETURN
END

A popular generative AI is suggesting to build a sql string and use 'EXEC'

CREATE FUNCTION dbo.MyTableValuedFunction (@myList VARCHAR(MAX))
RETURNS @myTable TABLE (
    [ID] INT,
    [Name] VARCHAR(MAX)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT ID, Name FROM MyTable WHERE ID IN (' + @myList + ')'

    INSERT INTO @myTable
    EXEC sp_executesql @sql
    RETURN
END

But when I try to run it, I receive the following error: Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Is there any way to use an IN clause with a list as an input parameter in a Table-Valued User-Defined Function?

Answer 1

IN expects either a list of scalar values or a SELECT statement that returns a single column. You can't pass it a variable and then expect SQL Server to treat it as a tuple. IN (@ScalarVariable) would be equivilent to = @ScalarVariable and IN (@TableVariable) would generate an error about an undefined scalar variable.

Instead, split your variable using STRING_SPLIT. Also, switch to an inline table value function; they are far more performant to a multi-line table value function (as you've written above):

CREATE FUNCTION dbo.MyTableValuedFunction (@MyList VARCHAR(MAX))
RETURNS table AS
RETURN SELECT ID,
              Name
       FROM dbo.MyTable
       WHERE ID IN (SELECT value
                    FROM STRING_SPLIT(@MyList,',')); --Comma (,) is assumed delimiter
GO
--Alternatively, use an `EXISTS`:
CREATE FUNCTION dbo.MyTableValuedFunction (@MyList VARCHAR(MAX))
RETURNS table AS
RETURN SELECT ID,
              Name
       FROM dbo.MyTable MT
       WHERE EXISTS (SELECT 1
                     FROM STRING_SPLIT(@MyList,',') SS --Comma (,) is assumed delimiter
                     WHERE SS.value = MT.ID); 

If a single ID can only appear in your variable (@MyList) once, then you could also use a JOIN:

CREATE FUNCTION dbo.MyTableValuedFunction (@MyList VARCHAR(MAX))
RETURNS table AS
RETURN SELECT ID,
              Name
       FROM dbo.MyTable MT
            JOIN STRING_SPLIT(@MyList,',') SS ON MT.ID = SS.value;

Answer 2

You should use a Table Valued Parameter. This works very similarly to a table variable.

First define a Table Type (note the addition of a primary key.

CREATE TYPE dbo.IdList AS TABLE (Id int PRIMARY KEY);

Then your function is simply

CREATE FUNCTION dbo.MyTableValuedFunction (@MyList dbo.IdList READONLY)
RETURNS TABLE
AS RETURN

SELECT
  t.ID,
  t.Name
FROM dbo.MyTable t
JOIN @MyList l ON l.ID = t.ID;

To use in SQL, you can just declare and insert into it.

DECLARE @MyList dbo.IdList;
INSERT @MyList (ID) VALUES (1),(2);
SELECT *
FROM dbo.MyTableValuedFunction (@MyList);

Client drivers normally have special features to pass Table Valued Parameters.