存储过程中的一个查询语句
我在存储过程中传入值(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)也没问题


------解决方案--------------------

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, ', '))