stored proc - executing a query with NOT IN where clause

Question

i have a stored procedure

Create PROCEDURE abc      
  @sRemovePreviouslySelectedWhereClause nvarchar(max)
AS
BEGIN

SELECT * 
      FROM table 
     WHERE nId NOT IN (@sRemovePreviouslySelectedWhereClause)

END;

The parameter @sRemovePreviouslySelectedWhereClause can have values like 0,1 . But this fails with error message:

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

Is there any other way to achieve this other than dynamic queries?

There is no SQL supported by any vendor I know of that supports using a variable to supply a comma delimited list for an IN/NOT IN clause. Use either dynamic SQL, or convert the list of values into a table valued function/etc to compare against. — OMG Ponies, Mar 01 '11 at 06:07
possible duplicate of [SQL Server SP - Pass parameter for "IN" array list?](http://stackoverflow.com/questions/537087/sql-server-sp-pass-parameter-for-in-array-list) — onedaywhen, Mar 01 '11 at 08:16
This is one of the most commonly asked questions. You will find many duplicates (or very close questions) on SO. — onedaywhen, Mar 01 '11 at 08:17

canon · Answer 1 · 2011-03-01T22:44:07.097

First, create a split function which splits your delimited string into a table:

CREATE FUNCTION [dbo].[Split]
(   
 @String varchar(max)
,@Delimiter char
)
RETURNS @Results table
(
 Ordinal int
,StringValue varchar(max)
)
as
begin

    set @String = isnull(@String,'')
    set @Delimiter = isnull(@Delimiter,'')

    declare
     @TempString varchar(max) = @String
    ,@Ordinal int = 0
    ,@CharIndex int = 0

    set @CharIndex = charindex(@Delimiter, @TempString)
    while @CharIndex != 0 begin     
        set @Ordinal += 1       
        insert @Results values
        (
         @Ordinal
        ,substring(@TempString, 0, @CharIndex)
        )       
        set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)     
        set @CharIndex = charindex(@Delimiter, @TempString)
    end

    if @TempString != '' begin
        set @Ordinal += 1 
        insert @Results values
        (
         @Ordinal
        ,@TempString
        )
    end

    return
end

Then change your where clause as follows:

select
 t.*
from [yourTable] t
where t.[ID] not in (select cast([StringValue] as int) from dbo.Split(@sRemovePreviouslySelectedWhereClause,','))

score 1 · Answer 2 · edited Mar 01 '11 at 10:54

Create FUNCTION [dbo].[fn_Split] (  
@List nvarchar(2000),   @SplitOn nvarchar(5)
 )   
RETURNS @RtnValue table  ( 
    Value nvarchar(100) ) 
 AS   
BEGIN 
While (Charindex(@SplitOn,@List)>0) 
Begin  
Insert Into @RtnValue (value)
 Select    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) 
End  
Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return 
END

**********
Create PROCEDURE abc      
  @sRemovePreviouslySelectedWhereClause nvarchar(max)
AS
BEGIN

SELECT * 
      FROM Table 
     WHERE nId NOT IN (select * from dbo.fn_Split(@sRemovePreviouslySelectedWhereClause,','))

END;

score 0 · Answer 3 · answered Mar 01 '11 at 06:06

0

You have to split the @sRemovePreviouslySelectedWhereClause parameter by ',' and place the resulting values in a temp table. Then your select would look like

select * from table where nId not in (select nId from #tempIds)

answered Mar 01 '11 at 06:06

Radu Caprescu

983
1
8
20

score 0 · Answer 4 · answered Mar 01 '11 at 06:46

This approach you're trying doesn't work. But if you're on SQL Server 2008, you could make use of the new features called Table Valued Parameters.

Basically, you create a user-defined table type

CREATE TYPE dbo.nIdTable AS TABLE(nID INT)

and you can then pass in multiple values in that TVP from the outside (e.g. from ADO.NET or such):

CREATE PROCEDURE abc(@idValues dbo.nIdTable READONLY)

and use that table variable inside your stored proc:

SELECT * 
FROM table 
WHERE nId NOT IN (SELECT nID FROM @idValues)

It seems that somebody has downvoted all the answers including the question itself. — Sachin Shanbhag, Mar 01 '11 at 09:18

score -1 · Answer 5 · answered Mar 01 '11 at 06:07

-1

You will need to use Dynamic sql for such kind of queries.

first construct the query and

SET @sql = 'select * from table 
where nId not in (' + @sRemovePreviouslySelectedWhereClause+ ')'

then use EXEC(@sql) to run the query.

answered Mar 01 '11 at 06:07

Sachin Shanbhag

54,530
11
89
103

This is precisely _not_ the place to use dynamic sql. You don't just go concatenating sql like that... it's dangerous and subject to sql injection. See my solution above. Dynamic sql is best used in a parameterized fashion ala `sp_executesql`. I didn't down-vote you, though. – canon Mar 01 '11 at 22:48

stored proc - executing a query with NOT IN where clause

5 Answers5

Linked