在存储过程中，怎么用not in

[youxihaha]

比如我想传入这样一个字符串(123,212,32)
然后select * from users where userid not in (123,212,32)

[j9988]

select * from users where charindex(','+rtrim(userid)+',' ,@字符串)>0

OR:

exec('select * from users where userid not in ('+@字符串+')')

[j9988]

有点错
select * from users where charindex(','+rtrim(userid)+',' ,','+@字符串+',')>0

[txlicenhe]

测试：
create table t1(a int)
insert t1 values(1)
insert t1 values(2)
insert t1 values(3)
insert t1 values(4)
go

create proc test @s varchar(20)
as
exec('select * from t1 where a not in '+@s)
go
-- 调用
exec test '(1,2)'
a
-----------
3
4

（所影响的行数为 2 行）

[youngby]

crate pro myPro
@1 varchar(20),
@2 varchar(20),
@3 varchar(20)
as
exec('select * from users where userid not in ('+@1+','+@2+','+@3+')')
go

[vileboy]

declare @krs table (NUM INT)
DECLARE @X VARCHAR(8000)
SET @X='123,212,32,'


WHILE @X<>''
BEGIN
INSERT INTO @KRS (NUM) VALUES (LEFT(@X,(CHARINDEX(',',@X)-1) ) )
SET @X=RIGHT(@X,LEN(@X)-CHARINDEX(',',@X))
END

select * from users where userid not in(select num from @drs)