我在存储过程中传入值(varchar型),用下面的语句无法实现.
declare @num varchar(50)
set @num='123,456,789'
select * from 表名 where user in (@num)
显示不出来.我打印出来看了下,如果是
set @num='123'没问题能执行
或者是:
set @num0='123'
set @num1='456'
set @num2='789'
select * from 表名 where user in(@num0,@num1,@num2)也没问题
declare @num varchar(50)
set @num='123,456,789'
select * from 表名 where user in (@num)
显示不出来.我打印出来看了下,如果是
set @num='123'没问题能执行
或者是:
set @num0='123'
set @num1='456'
set @num2='789'
select * from 表名 where user in(@num0,@num1,@num2)也没问题
set @num='123,456,789'
exec('select * from 表名 where user in ('+@num+')')
select * from 表名 where user in ('123,456,789')
而非
select * from 表名 where user in ('123','456','789')
可以试试
declare @num varchar(50)
set @num='123,456,789'
select * from 表名 where charindex(@num, user)>0
CREATE FUNCTION Common_Split (@inPattern As nvarchar(4000), @inDelimiter As char(1))
RETURNS @SplitResult TABLE
(
item sql_variant
)
AS
BEGIN
Declare @startPos As int
Declare @endPos As int
Declare @tempPattern As nvarchar(4000)
Declare @result As nvarchar(4000)
Set @startPos = 1
Set @endPos = 1
Set @result = ''
While @startPos <= Len(@inPattern)
Begin
Set @endPos = CharINDEX (@inDelimiter, @inPattern, @startPos)
if @endPos = 0
begin
Set @tempPattern = SubString(@inPattern, @startPos, Len(@inPattern))
Set @tempPattern = LTrim(RTrim(@tempPattern))
Insert into @SplitResult values (@tempPattern)
break
end
else
begin
Set @tempPattern = SubString(@inPattern, @startPos, (@endPos - @startPos + 1))
Set @tempPattern = REPLACE (@tempPattern , @inDelimiter , '' )
Set @tempPattern = LTrim(RTrim(@tempPattern))
Insert into @SplitResult values (@tempPattern)
Set @startPos = @endPos + 1
end
END
return
end 调用
select * from 表名 where user in (select item from Common_Split(@num,','))
set @num='123,456,789'
declare @num varchar(50)
set @num='123,456,789'
--------------------你這也寫的太複雜了,不用寫函數處理。直接用CharIndex或者Like都可以。
declare @num varchar(50)
set @num='123,456,789'
select * from 表名 where CharIndex([user], @num) > 0
--或者
select * from 表名 where @num Like '%' + [user] + '%'
set @num='123,456,789'
select * from 表名 where CharIndex(',' + Cast([user] As Varchar) + ',', ',' + @num + ',') > 0
--或者
select * from 表名 where ',' + @num + ',' Like '%,' + Cast([user] As Varchar) + ',%'
set @num='123,456,789'
select * from 表名 where charindex(user, @num)>0