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?

I think you can pass a user defined table type in as a parameter with the list of values. — Dale K, Jun 16 '23 at 10:30
Or you can find a string split function to split the values out for you... loads of examples out there. — Dale K, Jun 16 '23 at 10:38
@DaleK How I didn't think about that before?! Thank you. I found that function https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Hydraxize, Jun 16 '23 at 10:45
Why do you even need the function? just join the table directly and do the split on it — siggemannen, Jun 16 '23 at 11:27

score 1 · Accepted Answer · answered Jun 16 '23 at 10:42

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;

answered Jun 16 '23 at 10:42

Thom A

88,727
11
45
75

It's working like a charm! – Hydraxize Jun 16 '23 at 11:12
Why not use a Table Valued Parameter? – Charlieface Jun 16 '23 at 12:47
These are a bit of a pain with TVFs @Charlieface . It's fine if the OP is goign to use a static list of values, but such requirements are normally due to a denormalised design (in my experience) and you can't transform a delimited list of values in a column into a table type variable within a statement. `SELECT * FROM dbo.SomeTable CROSS APPLY dbo.MagicIntoTVP (DenormalisedColumn,',') TVP CROSS APPLY dbo.MyTableValuedFunction(TVP.TableTypeValue)` isn't possible. – Thom A Jun 16 '23 at 12:56
No in that case you would just do `CROSS APPLY dbo.MyTableValuedFunction(TVP.ID)` and make the TVF accept one ID at a time,. The performance would be the same. – Charlieface Jun 16 '23 at 13:04
@Charlieface It was suggested by Dale K and I thought about it before posting (I already have few TVFs using Table-Valued parameters). It’s a viable solution but it would have required more changes on the client side. The client receives a comma separated list so the split function integrates well. I know it will be a short that will never exceed 20 elements. That’s why I went for the split function for this specific case. – Hydraxize Jun 17 '23 at 21:29

score 1 · Answer 2 · answered Jun 16 '23 at 12:49

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.

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

2 Answers2