TSQL Statement IN

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 开发者_StackOverflow社区below, I am sure this is pretty simple!

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

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.

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)

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.

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