1CREATE TYPE dbo.IDList
2AS TABLE
3(
4 ID INT
5);
6GO
7
8CREATE PROCEDURE dbo.DoSomethingWithEmployees
9 @List AS dbo.IDList READONLY
10AS
11BEGIN
12 SET NOCOUNT ON;
13
14 SELECT ID FROM @List;
15END
16GO
1CREATE FUNCTION dbo.SplitInts
2(
3 @List VARCHAR(MAX),
4 @Delimiter VARCHAR(255)
5)
6RETURNS TABLE
7AS
8 RETURN ( SELECT Item = CONVERT(INT, Item) FROM
9 ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
10 FROM ( SELECT [XML] = CONVERT(XML, '<i>'
11 + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
12 ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
13 WHERE Item IS NOT NULL
14 );
15GO