关于存储过程的一点疑问

[allen0118]

这个存储过程是将用户传入进来的字符串分开插入到数据库里面,
语句如下: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

[以学习为目的]

先SF，慢慢看

[以学习为目的]

先SF，慢慢看

[人生无悔]

 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)),'')  --加这句就可以了

[人生无悔]

因你新增一条后,另一条重复的已不符合条件了,所以都会不处理,不死循环才怪

[以学习为目的]

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
 else SELECT @SN_N=REPLACE(@SN_N,LEFT(@SN_N,CHARINDEX(',',@SN_N)),'')  --加这句试试


END

[allen0118]

[Quote=引用 3 楼 的回复:]
SQL code

IF NOT EXISTS(SELECT * FROM PK2 WHERE SN=LEFT(@SN_N,CHARINDEX(',',@SN_N)-1))
BEGIN

INSERT INTO TestTB(SN,CartonSN,LineName,UserID,CreateDate)
VAL……
[/Quote]

感谢，确实是这个问题！

[以学习为目的]

好邪恶的404，你懂得


你的最外层BEGIN...END程序块，当有记录时，满足CHARINDEX(',',@SN_N)>0

[koumingjie]

WHILE CHARINDEX(',',@SN_N)>0
BEGIN
 
    IF NOT EXISTS(SELECT * FROM TestTB 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())   
        
        IF @@ERROR<>0
        BEGIN
            SELECT  FERRDESC='数据保存失败'
            RAISERROR(@ERRDESC,16,1)
            ROLLBACK TRAN
            RETURN
        END
    END
    
    SET @SN_N=REPLACE(@SN_N,LEFT(@SN_N,CHARINDEX(',',@SN_N)),'') -----放到这里   
    
END

COMMIT TRAN