SQL 问题 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

Question

--循环每行的id
declare @num int
--被拆的字段名
declare @dd nvarchar(50)
declare @ss nvarchar(50)
--接收值
declare @name nvarchar(50)
--要填充的列名
declare @bb nvarchar(50)
declare @ee nvarchar(50)
declare @cc nvarchar(50)
declare @ff nvarchar(50)
--给列名赋值.只需修改他们的值
set @bb='a'
set @ee='c'
set @cc='b'
set @ff='d'
set @dd='pp'
set @ss='aa'

set @num=1
while((select count(*) from test)>@num)
begin
select @name=@dd from test where id =@num

update test set
@dd=null,
@bb=(select SUBSTRING(SUBSTRING( @name,1,charindex('+',@name)),2,CHARINDEX('+',SUBSTRING( @name,1,charindex('+',@name)))-2)
from test where pp=@name),
@cc=(select substring(substring( @name,0,charindex('-',@name)),charindex('+',substring( @name,0,charindex('-',@name)))+1,20)
from test where pp=@name),
@ee=(select substring(substring( @name,CHARINDEX('-',@name)+2,20),CHARindex('-',substring( @name,CHARINDEX('-',@name)+1,20))+1,
CHARINDEX('+',substring( @name,CHARINDEX('-',@name)+2,20))-1)
from test where @dd=@name),
@ff=(select SUBSTRING( @name,charindex('+',@name,CHARINDEX('-',@name))+1,50) from test where @dd=@name)
where @dd=@name
set @num=@num+1
end

Answer 1

id pp a b c d
1 K100+200+300+400 NULL NULL NULL NULL
2 K200+300+400+500 NULL NULL NULL NULL
3 K300+400+500+600 NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL

需要将pp值 拆分为四个纯数字 录入到abcd 字段的时候报错，
希望a,b,c,d的结果是 100 200 300 400