How to pass delimited list to a Stored Procedure

Let’s write a Table-valued function below. This can be created by navigating to Functions – Table-Valued Functions inside SQL Management Studio.

Just an FYI, I am using the AdventureWorks database, but this  sample can be used in any situation.

CREATE FUNCTION [dbo].[fn_SplitStateProvinceCode]
(
   @List VARCHAR(MAX),
   @Delimiter CHAR(1)
)
RETURNS TABLE 
AS 
  RETURN ( SELECT Item = CONVERT(varchar(500), Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, ''
        + REPLACE(@List, @Delimiter, '') + '').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );

For testing, you can declare a variable and test the statement by passing the @List and delimiter like so. As you can see, in the sample below, the delimiter is ‘,’.

Caution: The delimited string is strictly delimited by a comma and has no space in between province codes.

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
SELECT CODES = ITEM FROM dbo.[fn_SplitStateProvinceCode (@List, ',') 

If you wish to write it in a query using the Where clause and using ‘IN’ you can do the following:

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
Select * from Person.StateProvince 
WHERE StateProvinceCode IN (Select CODES = ITEM FROM dbo.fn_SplitStateProvinceCode (@List, ','))	

Cheers,

Obi

About Obi Oberoi

Obi Oberoi is an Independent Consultant, Developer and a Life-Long student of continuous learning. Obi enjoys to code, read, and hang out with techies and geeks!
This entry was posted in SQL Server. Bookmark the permalink.