How to use table-valued function in SQL Data Source

[Chris]

I can't get any columns to show in the Data Explorer when setting up a SQL Data Source with a command text that calls a SQL TVF function.

My example/test function is as follows:

CREATE OR ALTER FUNCTION dbo.Test(
	@CommaSeparatedValue varchar(max)
)
RETURNS @Items TABLE(
	ItemValue numeric(18,0)
)
AS
BEGIN
	INSERT INTO @Items
	SELECT
		Items.ItemValue
	FROM dbo.StringSplit(@CommaSeparatedValue , ',') Split
	LEFT JOIN Items ON TRY_CAST(Split.[Value] AS numeric(18,0)) = Items.ItemValue
	
	RETURN
END

And my set up for the datasource object:

 

Columns show as expected when querying a table or view, but TVF functions do not seem to work. In Expressions, I cannot see any available Fields.

[Neli]

Hi Chris,

You can test the approach from the Configuring Stored Procedure with Temporary Tables KB article.