How to place CROSS APPLY inside a Multi-statement Table-valued Functions in T-SQL

I have a table Source that contains a column tags that has a string data type.

Here is an example of a row from the column:

["tag_a", "tag_b", "tag_c", "tag_d"]

The following code extracts each tag and creates a new column through a cross apply with the original tag list.

SELECT
DISTINCT tags as TAGS_LIST,
TRIM(' "[]' from value) as TAG
FROM
DB.dbo.Source
CROSS APPLY
STRING_SPLIT(tags, ',');

The previous example would yield the following table:

TAGS_LIST	TAG
["tag_a", "tag_b", "tag_c", "tag_d"]	tag_a
["tag_a", "tag_b", "tag_c", "tag_d"]	tag_b
["tag_a", "tag_b", "tag_c", "tag_d"]	tag_c
["tag_a", "tag_b", "tag_c", "tag_d"]	tag_d

It works fine on its own.

However, I am unable to fit it into a Multi-statement Table-valued Function:

CREATE FUNCTION [dbo].[fnTAGS](@STARTDATE DATETIME, @ENDDATE DATETIME) 
RETURNS TABLE 
AS 
RETURN
    (
SELECT
DISTINCT tags as TAGS_LIST,
TRIM(' "[]' from value) as TAG
FROM
DB.dbo.Source
WHERE
DB.dbo.Source.[creation_date] >= @STARTDATE AND
DB.dbo.Source.[creation_date] <= @ENDDATE
CROSS APPLY
STRING_SPLIT(tags, ',')
    );

This gives me the following error: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.

Placing it before WHERE also doesn’t work.

I could not find any specification telling me CROSS APPLY could not be placed inside a Multi-statement Table-valued Function.

How can I have the CROSS APPLY inside of a Multi-statement Table-valued Function?

I have tried positioning the cross apply in different places of the functions.

I have tried researching on CROSS APPLY inside of a Multi-statement Table-valued Function, but have only found articles that talk about them as separate.

You need to sign in to view this answers