TSQL Statement IN

Question

I am having a small problem with the IN SQL statement. I was just wondering if anyone could help me?

@Ids = "1,2,3,4,5"

SELECT * FROM Nav WHERE CONVERT(VARCHAR,NavigationID) IN (CONVERT(VARCHAR,@Ids))

This is coming back with the error below, I am sure this is pretty simple!

Conversion failed when converting the varchar value '1,' to data type int.

OMG Ponies · Answer 1 · 2010-12-18T20:37:24.367

4

The SQL IN clause does not accept a single variable to represent a list of values -- no database does, without using dynamic SQL. Otherwise, you could use a Table Valued Function (SQL Server 2000+) to pull the values out of the list & return them as a table that you can join against.

Dynamic SQL example:

EXEC('SELECT * 
        FROM Nav 
       WHERE NavigationID IN ('+ @Ids +')')

I recommend reading The curse and blessings of dynamic SQL before using dynamic SQL on SQL Server.

edited Dec 18 '10 at 20:37

answered Dec 18 '10 at 20:32

OMG Ponies

325,700
82
523
502

As a side note, dynamic queries are not well handled by the queries optimizer, but that was not the question. – A. M. Dec 18 '10 at 20:39
1

@PulsarBlow: "well handled" is vague, and incorrect - there's no difference between a dynamic and a non-dynamic query to the optimizer. `EXEC` however has been known to not cache query plans, while `sp_executesql` does. I recommend you read the link about the curse & blessings of dynamic sql. – OMG Ponies Dec 18 '10 at 20:44

score 1 · Answer 2 · answered Dec 18 '10 at 20:42

Jason:

First create a function like this

Create     FUNCTION [dbo].[ftDelimitedAsTable](@dlm char, @string varchar(8000))
RETURNS 
--------------------------------------------------------------------------*/
/*------------------------------------------------------------------------
declare @dlm  char, @string varchar(1000)
set @dlm=','; set @string='t1,t2,t3';
-- tHIS FUNCION RETUNRS IN THE ASCENDING ORDER
-- 19TH Apr 06
------------------------------------------------------------------------*/
--declare
    @table_var TABLE 
    (id int identity(1,1),
        r varchar(1000) 
     )
AS
BEGIN
    declare @n int,@i int
    set @n=dbo.fnCountChars(@dlm,@string)+1
    SET @I =1
    while @I <= @N
        begin
            insert @table_var
                select dbo.fsDelimitedString(@dlm,@string,@i)
            set @I= @I+1

        end
    if @n =1 insert @TABLE_VAR VALUES(@STRING)
    delete  from @table_var where r=''
    return
END

And then

set quoted_identifier off
declare @ids varchar(max)
select @Ids = "1,2,3,4,5"
declare @nav table ( navigationid int identity(1,1),theother bigint)
insert @nav(theother) select 10 union select 11 union select 15
SELECT * FROM @Nav WHERE CONVERT(VARCHAR,NavigationID) IN (select id from dbo.ftDelimitedAsTable(',',@Ids))

select * from dbo.ftDelimitedAsTable(',',@Ids)

score 0 · Answer 3 · answered Dec 18 '10 at 20:33

0

What you're doing is not possible with the SQL IN statement. You cannot pass a string to it and expect that string to be parsed. IN is for specific, hard-coded values.

answered Dec 18 '10 at 20:33

mattmc3

17,595
7
83
103

score 0 · Answer 4 · edited May 23 '17 at 10:33

There are two ways to do what you want to do here. One is to create a 'dynamic sql' query and execute it, after substituting in your IN list.

DECLARE @query varchar(max);
SET @query = 'SELECT * FROM Nav WHERE CONVERT(VARCHAR,NavigationID) IN (' + @Ids + ')'
exec (@query)

This can have performance impacts and other complications. Generally I'd try to avoid it.

The other method is to use a User Defined Function (UDF) to split the string into its component parts and then query against that. There's a post detailing how to create that function here

Once the function exists, it's trivial to join onto it

SELECT * FROM Nav
CROSS APPLY dbo.StringSplit(@Ids) a
WHERE a.s = CONVERT(varchar, Nav.NavigationId)

NB- the 'a.s' field reference is based on the linked function, which stores the split value in a column named 's'. This may differ based on the implementation of your string split function

This is nice because it uses a set based approach to the query rather than an IN subquery, but a CROSS JOIN may be a little complex for the moment, so if you want to maintain the IN syntax then the following should work:

SELECT * FROM Nav
WHERE Nav.NavigationId IN
    (SELECT CONVERT(int, a.s) AS Value
    FROM dbo.StringSplit(@Ids) a

TSQL Statement IN

4 Answers4