I have found many solutions to pass a list parameter from ColdFusion to a stored procedure,
and this one is a very simple and efficient way to convert a string to a table. Thanks to Amit Gaur posting to SQL ServerCentral.

I modified his original function which returns only integers (forcing you to pass only a list of integers).

Suppose you pass the string ‘2A354,FB452,8896’ in the argument @itemList as a list of item codes you want to retrieve from your inventory table.

In your stored procedure you would have this query:
{code type=sql}SELECT item, description, price
FROM inventory
WHERE item IN (SELECT item FROM strToTable(@itemList)){/code}

Here is the function strToTable():

{code type=sql}SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Amit Gaur
— Create date: July 25th 2008
— Description:    Convert a string to a table
— =============================================
CREATE FUNCTION [dbo].[strToTable]
(
@array varchar(max),
@del char(1)
)
RETURNS
@listTable TABLE
(
item int
)
AS
BEGIN

WITH rep (item,list) AS
(
SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) – 1) as item,
SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list

UNION ALL

SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) – 1) as item,
SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list
FROM rep
WHERE LEN(rep.list) > 0
)
INSERT INTO @listTable
SELECT item FROM rep

RETURN
END

GO{/code}