在将 nvarchar 值 '35,36' 转换成数据类型 int 时失败

[icanjj]

我这样写就能运行：
select * from Item where ItemId in (35,36)

但如果将in里的35,36做为参数转过来，就出错，例如：

declare @st nvarchar(2000)
这里必须是nvarchar类型
select @st='35,36'
select * from Item where ItemId in (@st)
提示：在将 nvarchar 值 '35,36' 转换成数据类型 int 时失败

怎么解决呢？

[水族杰纶]

select @st='''35''+'',36'''
exec('select * from Item where ItemId in '+@st)

[csdyyr]

exec('select * from Item where ItemId in ('+@t+')')

[中国风]

或

declare @st nvarchar(2000)
select @st='35,36'
select * from Item where patindex('%,'+rtrim(ItemId)+',%',','+@st+',')>0

[hyde100]

动态sql

select @st='''35''+'',36'''
exec('select * from Item where ItemId in '+@st)

[pt1314917]

declare @st nvarchar(2000) 
select @st='35,36' 
select * from Item where charindex(','+ltrim(ItemId)+',',','+@st+',')>0 

[Cyril_Tam]

换种方式行不?

declare @first nvarchar(2000)
declare @second nvarchar(2000)
select @first='2'
select @second='3'
select * from table1 where id between @first and @second

[tim_spac]

另一种方式:

if exists (select 1 from sysobjects where name='fn_list2tb)
    drop function dbo.fn_list2tb
go
create function dbo.fn_list2tb (@lst varchar(512))
returns @tb table (id int) as
begin
  declare @pos int
  set @lst = replace(replace(@lst,' ',''),',,',',')
  set @pos = charindex(',',@lst)
  while @pos>0 and @lst>'' begin
	if @pos>1
	    insert into @tb values (left(@lst,@pos-1))
    set @lst = stuff(@lst,1,@pos,'')
	set @pos = charindex(',',@lst)
  end
  if @lst>''
    insert into @tb values (@lst)
  return
end
go

select a.*
from Item a
join dbo.fn_lst2tb(@lst) b on a.itemId=b.id