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
