integerListToTable.sql

/*
*******	Run on both main and US Soccer databases ********
*/	

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.integerListToTable ( @strString varchar(max))
RETURNS  @Result TABLE(LIST_ID BIGINT)
AS
/*------------------------------------------------------------------
** Name:  [dbo.integerListToTable]
** Description : Converts a comma-delimited list of integers to a table.
** Example Call :
*   DECLARE @strString varchar(max);
*   SET @strString = '1,2,3,4,5';
*	select * from dbo.integerListToTable(@strString);
* | LIST_ID |
* | ------- |
* | 1 |
* | 2 |
* | 3 |
* | 4 |
* | 5 |
********************************************************************
MODIFIED DATE 	MODIFIED BY 		DESCRIPTION OF REVISION
2018-08-23		  Adrian Moreno	  Creation
********************************************************************
------------------------------------------------------------------*/
BEGIN
	DECLARE @x XML 
	SELECT @x = CAST('<A>'+ REPLACE(@strString,',','</A><A>')+ '</A>' AS XML)
	INSERT INTO @Result 
	SELECT 
		t.value('.', 'int') AS inVal
	FROM 
		@x.nodes('/A') AS x(t)
	RETURN
END   
GO