java.sql.SQLException: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效

java.sql.SQLException: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:636)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:780)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.eline.orm.hibernate.SimpleHibernateDao.queryByProc(SimpleHibernateDao.java:199)
    at org.eline.service.dis.WarinManager.saveWarin(WarinManager.java:1002)
    at org.eline.service.dis.WarinManager$$FastClassByCGLIB$$9547e738.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
    at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
    at org.eline.service.dis.WarinManager$$EnhancerByCGLIB$$31e15384.saveWarin(<generated>)
    at dis.WarinManagerTest.testSaveWarin(WarinManagerTest.java:42)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at junit.framework.TestCase.runTest(TestCase.java:164)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runManaged(AbstractJUnit38SpringContextTests.java:307)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.access$000(AbstractJUnit38SpringContextTests.java:94)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests$1.run(AbstractJUnit38SpringContextTests.java:193)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runTest(AbstractJUnit38SpringContextTests.java:270)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runTestTimed(AbstractJUnit38SpringContextTests.java:228)
    at org.springframework.test.context.junit38.AbstractJUnit38SpringContextTests.runBare(AbstractJUnit38SpringContextTests.java:191)
    at junit.framework.TestResult$1.protect(TestResult.java:106)
    at junit.framework.TestResult.runProtected(TestResult.java:124)
    at junit.framework.TestResult.run(TestResult.java:109)
    at junit.framework.TestCase.run(TestCase.java:120)
    at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)

    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

stored procedured code:

USE [hb_lottery]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROCEDURE dis_insertWari
ALTER PROCEDURE [dbo].[dis_insertWari]
    --@ProductId_Array varChar(800),
    --@ModuleId int,
    @delivery varchar(50), --@usid bigint,
    @postationid varchar(50),    --@stid varchar(50)
    @sn varchar(500)
AS
    DECLARE @PointerPrev int
    DECLARE @PointerCurr int
    DECLARE @TSn varchar(50)
    DECLARE @usid bigint,@stid varchar(50),@waid varchar(50),@dgid varchar(50)
    
    select @usid = usid from usr_users where username = @delivery  --'shoulijun'
    select @stid = stid from sta_station where @postationid = @postationid   --5400010240-- postationid --5480011708
    --select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @sn --3500950151246
    
    Set @PointerPrev=1
    set @PointerCurr=1
    
    begin transaction
    Set NoCount ON
    --delete  from ProductListSpecial where ModuleId=@ModuleId
    
    --first
    Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)
    set @TSn=SUBSTRING(@sn,@PointerPrev,@PointerCurr-@PointerPrev)
    --Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
    select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
    insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)
    
    --second
    SET @PointerPrev = @PointerCurr
    while (@PointerPrev+1 < LEN(@sn))
    Begin
        Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)
        if(@PointerCurr>0)
        Begin
            set @TSn=SUBSTRING(@sn,@PointerPrev+1,@PointerCurr-@PointerPrev-1)
            --Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
            select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
            insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)
            SET @PointerPrev = @PointerCurr
        End
        else
            Break
    End
    
    --third
    set @TSn=SUBSTRING(@sn,@PointerPrev+1,LEN(@sn)-@PointerPrev)
    --Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
    select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
    insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid) values(NEWID(),@waid,@dgid,@stid,@sn,GETDATE(),@usid,@usid)
    Set NoCount OFF
    if @@error=0
    begin
        commit transaction
    end
    else
    begin
        rollback transaction
    end
    select 'ok'

把几处Set @PointerCurr=CharIndex(',',@sn,@PointerPrev+1)改为Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)即可。

PS:哎！copy，paste疏忽大意，没改过来。