我这样写就能运行:
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 * 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 时失败怎么解决呢?
exec('select * from Item where ItemId in '+@st)
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
select @st='''35''+'',36'''
exec('select * from Item where ItemId in '+@st)
declare @st nvarchar(2000)
select @st='35,36'
select * from Item where charindex(','+ltrim(ItemId)+',',','+@st+',')>0
declare @second nvarchar(2000)
select @first='2'
select @second='3'
select * from table1 where id between @first and @second
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
goselect a.*
from Item a
join dbo.fn_lst2tb(@lst) b on a.itemId=b.id