SQL - Get CSV string from table value parameter

get-csv-from-table-value-parameter.sql

/* --------------------------------------------------------------------------------------------------
Create the type for the table valued parameter
----------------------------------------------------------------------------------------------------*/
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'StringTable' AND ss.name = N'dbo')
DROP TYPE [dbo].[StringTable]
GO

CREATE TYPE [dbo].[StringTable] AS TABLE(
	[TextValue] [varchar](max) NULL
)
GO

/* --------------------------------------------------------------------------------------------------
Create the funtion
----------------------------------------------------------------------------------------------------*/
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'dbo.GenerateDelimitedString' AND type = 'F')
	DROP FUNCTION dbo.fn_GenerateDelimitedString
GO
CREATE FUNCTION dbo.fn_GenerateDelimitedString
(
	@inputData StringTable READONLY
	, @delimiter VARCHAR(10)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @returnValue VARCHAR(MAX);
	
	SELECT @returnValue = LTRIM(RTRIM(SUBSTRING((SELECT @delimiter + TextValue FROM @inputData FOR XML PATH('')), LEN(@delimiter) + 1, 2000000)))
	
	RETURN @returnValue;
END
GO


/* --------------------------------------------------------------------------------------------------
Try it...
----------------------------------------------------------------------------------------------------*/
DECLARE @strings StringTable;

INSERT INTO @strings VALUES
('red')
, ('blue')
, ('green')

SELECT * FROM @strings

DECLARE @csv VARCHAR(MAX)

SELECT @csv = dbo.fn_GenerateDelimitedString(@strings, ';');

SELECT @csv