解决SQLSEVER 存储过程入参不能带入in的参数传带逗号值的办法

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

即可解决。