这个存储过程是将用户传入进来的字符串分开插入到数据库里面,
语句如下:usp_Insert '0129100001,0129100002,0129100003,0129100004,0129100005,','C00001','LINE3','admin'
目地是将0129100001,0129100002,0129100003,0129100004,0129100005分开插入到表里面,第一次插入的时候没有问题,
但是如果数据库里面有重复的记录的话这个存储过程就变成死循环了,不知道是不是跟那个循环变量赋值的时候有问题呢?

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='usp_Insert')
DROP PROC usp_Insert
GO
CREATE PROCEDURE usp_Insert
    @SN VARCHAR(1024)=' ',
    @CartonSN VARCHAR(20)=' ',
    @LineName VARCHAR(5)=' ',
    @UserID VARCHAR(10)=' '
AS


--usp_Insert '0129100001,0129100002,0129100003,0129100004,0129100005,','C00001','LINE3','admin'
 
 /*这一部分就是处理之后的SQL语句，
INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) VALUES('0129100001','C00001','LINE3','admin',GETDATE())
 
INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) VALUES('0129100002','C00001','LINE3','admin',GETDATE())
  
INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) VALUES('0129100003','C00001','LINE3','admin',GETDATE())
   
INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) VALUES('0129100004','C00001','LINE3','admin',GETDATE())
      
INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) VALUES('0129100005','C00001','LINE3','admin',GETDATE())
*/


BEGIN TRAN
DECLARE @SN_N VARCHAR(1024),@ERRDESC VARCHAR(100)
SELECT @SN_N =@SN

WHILE CHARINDEX(',',@SN_N)>0
BEGIN
 
    IF NOT EXISTS(SELECT * FROM PK2 WHERE SN=LEFT(@SN_N,CHARINDEX(',',@SN_N)-1))    
    BEGIN    
    
        INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) 
            VALUES(LEFT(@SN_N,CHARINDEX(',',@SN_N)-1),@CartonSN, @LineName,@UserID,GETDATE())   
        SET @SN_N=REPLACE(@SN_N,LEFT(@SN_N,CHARINDEX(',',@SN_N)),'')    
        IF @@ERROR<>0
        BEGIN
            SELECT  FERRDESC='数据保存失败'
            RAISERROR(@ERRDESC,16,1)
            ROLLBACK TRAN
            RETURN
        END
    END
END

COMMIT TRAN

 IF NOT EXISTS(SELECT * FROM PK2 WHERE SN=LEFT(@SN_N,CHARINDEX(',',@SN_N)-1))    
    BEGIN    
    
        INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate) 
            VALUES(LEFT(@SN_N,CHARINDEX(',',@SN_N)-1),@CartonSN, @LineName,@UserID,GETDATE())   
        SET @SN_N=REPLACE(@SN_N,LEFT(@SN_N,CHARINDEX(',',@SN_N)),'')    
        IF @@ERROR<>0
        BEGIN
            SELECT  FERRDESC='数据保存失败'
            RAISERROR(@ERRDESC,16,1)
            ROLLBACK TRAN
            RETURN
        END
    END
else SET @SN_N=REPLACE(@SN_N,LEFT(@SN_N,CHARINDEX(',',@SN_N)),'')  --加这句就可以了