ALTER PROCEDURE [dbo].[P_UpdateUserLeftRightV2]
@UserId varchar(50),
@TotalBonus decimal(18,2),
@LeftUsers varchar(MAX),
@RightUsers varchar(MAX)
AS
BEGIN
SET NOCOUNT ON;
begin tran
begin
if @LeftUsers <> ''
begin
update [User] set LeftTotal=LeftTotal+@TotalBonus,LeftNew=LeftNew+1 where UserID in (<span style="font-family: Arial, Helvetica, sans-serif;">@LeftUser</span><span style="font-family: Arial, Helvetica, sans-serif;">);</span>
end
if @LeftUsers <> ''
begin
update [User] set RightTotal=RightTotal+@TotalBonus,RightNew=RightNew+1 where UserID in (@<span style="font-family: Arial, Helvetica, sans-serif;">RightUsers</span><span style="font-family: Arial, Helvetica, sans-serif;">);</span>
end
update [User] set isupdate = 1 where userid=@UserId;
end
if @@ERROR<>0
rollback tran
else
commit tran
SET NOCOUNT OFF;
END
如上代码:入参 LeftUsers和@RightUsers 是一个字符串,类似 11,22,33,调用后不能实现效果,where不能成立
解决这个问题,可以使用函数来处理,把入参数据构成列在函数的表值函数下面,函数如下
create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
如何调用如下:select col from [dbo].[f_split](@LeftUsers,',')
ALTER PROCEDURE [dbo].[P_UpdateUserLeftRightV2]
@UserId varchar(50),
@TotalBonus decimal(18,2),
@LeftUsers varchar(MAX),
@RightUsers varchar(MAX)
AS
BEGIN
SET NOCOUNT ON;
begin tran
begin
if @LeftUsers <> ''
begin
update [User] set LeftTotal=LeftTotal+@TotalBonus,LeftNew=LeftNew+1 where UserID in (select col from [dbo].[f_split](@LeftUsers,','));
end
if @LeftUsers <> ''
begin
update [User] set RightTotal=RightTotal+@TotalBonus,RightNew=RightNew+1 where UserID in (select col from [dbo].[f_split](@RightUsers,','));
end
update [User] set isupdate = 1 where userid=@UserId;
end
if @@ERROR<>0
rollback tran
else
commit tran
SET NOCOUNT OFF;
END
即可解决。