Pass CSV parameter in SQL Stored procedure

Recently, I have been asked to prepare query that accept CSV value and parse that value in stored procedure.

In early, I didn’t give much attention as I thought it was just like the Where clause with IN. If we prepare dynamic query and execute it works well. But we don’t want to do that, we want some solution that should work every time when we pass CSV value.

The solution was to create function and it returns list of value. Here is the function; I have found this function during my search.

CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000))

RETURNS @Result TABLE (Value varchar(30))

AS

BEGIN

DECLARE @List TABLE

(

Value varchar(30)

)

DECLARE

@Value varchar(30),

@Pos int

SET @CSV = LTRIM(RTRIM(@CSV))+ ','

SET @Pos = CHARINDEX(',', @CSV, 1)

IF REPLACE(@CSV, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

IF @Value <> ''

INSERT INTO @List (Value) VALUES (@Value)

SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)

SET @Pos = CHARINDEX(',', @CSV, 1)

END

END

INSERT @Result

SELECT

Value

FROM

@List

RETURN

END

Now say for example you want to create stored procedure that should return you all state name where we passed id with CSV,

We can pass any number of state id in the @StateId parameter.

CREATE PROCEDURE State_Search

@StateId nvarchar(2000)

AS

BEGIN

SELECT * FROM tblState

WHERE tblState.StateId IN (SELECT * FROM dbo.CSVToLIst(@StateId))

END