SELECT
	c.contact_id,
	c.firstname + ' ' + c.lastname + ' <' + c.email + '>' AS contact
FROM
	contacts c
WHERE
	c.contact_id IN (5,9,15)

CREATE PROCEDURE dbo.sp_MyArraySProc
(
	@ContactList	nvarchar(1000)
)
AS
SET NOCOUNT ON
SELECT
	c.contact_id,
	c.firstname + ' ' + c.lastname + ' <' + c.email + '>' AS contact
FROM
	contacts c
WHERE
	c.contact_id  IN (@ContactList)
RETURN

CREATE PROCEDURE dbo.sp_MyArraySProc
(
	@ContactList	nvarchar(1000)
)
AS
SET NOCOUNT ON
SELECT
	c.contact_id,
	c.firstname + ' ' + c.lastname + ' <' + c.email + '>' AS contact
FROM
	contacts c
WHERE
	CAST(c.contact_id AS VARCHAR) IN (@ContactList)
RETURN

ALTER PROCEDURE dbo.sp_GetContacts
(
	@ContactList	varchar(1000)
)
AS
SET NOCOUNT ON

-- @ContactList is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @Separator varchar(1)
SELECT @Separator = ','

DECLARE @separator_position int -- This is used to locate each separator character
DECLARE @array_value varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end.  I always look to the
-- left of the separator character for each array value
-- SET @ContactList = @ContactList + @Separator
-- I already have a trailing comma in mine

-- Create a table to hold the new values
CREATE TABLE #ContactTable (contact_id int, contact nvarchar(251))

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%' , @ContactList) <> 0 
BEGIN
	-- patindex matches the a pattern against a string
	SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @ContactList)
	SELECT @array_value = LEFT(@ContactList, @separator_position - 1)
	
	INSERT INTO #ContactTable
		SELECT
			c.contact_id,
			c.firstname + ' ' + c.lastname + ' <' + c.email + '>' AS contact
		FROM
			contacts c
		WHERE
			c.contact_id  = @array_value

	-- This replaces what we just processed with an empty string
	SELECT @ContactList = STUFF(@ContactList, 1, @separator_position, '')
END

SELECT * FROM #ContactTable ORDER BY contact
DROP TABLE #ContactTable
RETURN