Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In
I have a SQL Server Stored Procedure where I would like to pass a varchar
full of comma delimited values to an IN
function. For example:
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE tableid IN (@Ids);
This does not work of course. I get the error:
Conversion failed when converting the varchar value '1,2,3,5,4,6,7,98,234' to data type int.
How can I accomplish this (or something relatively similar) without resorting to building dynamic SQL?
Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!
Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
use this function to split your string, which does not loop and is very fast:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
RETURN
END --Function FN_ListToTable
you can use this function as a table in a join:
SELECT
Col1, COl2, Col3...
FROM YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
Here is your example:
Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)
What do you thik the Query proceser is doing, when you execute your Select statement? - generating all the rows instantaneously using trans-temporal quantumn physics? It's also looping... You are just changing from a loop you explicitly control, to one the SQL Server Query processer controls...
@Charles Bretana, Ha! You can write code 10 different ways, and each will perform differently (speed wise). The goal is to write it the way that will run the fastest. Just try it out, run this split method against the stored procedure looping method listed in another question. Run each 100 times, and see how long they take. ----- FYI, I'm sure the SQL Server internal looping is MUCH faster and better optimized than a user created stored procedure, with local variables and a WHILE loop!
Do you have a solution for more than 8000 characters? A few of the places I've needed this have hit the 8000 character limitation so I wrote the implementation I linked above.
@Will Rickards, if you need to handle strings >8k, you could make your loop faster by using a CLR (sommarskog.se/arrays-in-sql.html) or change your loop to process chunks of 8k (make sure you break on commas), but pass those chunks into a function like mine.
Charles and KM. There is some merit in each of your comments. Yes, the SQL engine will, at some point, loop through the individual numbers. But the engine's loop will likely run much faster than a user written loop. The real solution, to avoid looping in the first place is to redesign the schema to comply with first normal form. The CSV field looks like 1NF, but it isn't really 1NF. That's the real problem.