ALTER PROCEDURE ExamTime_ADD
@e_lessonid char(6),
@e_year VARCHAR(200),
@e_date VARCHAR(500),
@e_examtimeStart varchar(500),
@e_examtimeEnd varchar(500),
@e_createrID int
AS
DECLARE @START VARCHAR(500),@STARTRI VARCHAR(40)
DECLARE @ENDS VARCHAR(500),@ENDSTRI VARCHAR(40)
DECLARE @EDATE VARCHAR(500),@EDATETRI VARCHAR(15)
DECLARE @EYEAR VARCHAR(500),@EYEARRTI VARCHAR(5)
DECLARE @NUMS INT
BEGIN TRANSACTION
SET @START = @e_examtimeStart + ','
SET @ENDS = @e_examtimeEnd + ','
SET @EDATE = @e_date + ','
SET @EYEAR = @e_year + ','
SET @NUMS=CHARINDEX(',',@START)
WHILE @NUMS>0
BEGIN
SET @STARTRI=LEFT(@START,CHARINDEX(',',@START)-1)
SET @ENDSTRI=LEFT(@ENDS,CHARINDEX(',',@ENDS)-1)
SET @EDATETRI=LEFT(@EDATE,CHARINDEX(',',@EDATE)-1)
SET @EYEARRTI=LEFT(@EYEAR,CHARINDEX(',',@EYEAR)-1)
INSERT INTO [e_ExamTime](
[e_lessonid],[e_year],[e_date],[e_examtimeStart],[e_examtimeEnd],[e_createrID]
)VALUES(
@e_lessonid,@EYEARRTI,@EDATETRI,@STARTRI,@ENDSTRI,@e_createrID
)
SET @ENDS=SUBSTRING(@ENDS,CHARINDEX(',',@ENDS)+1,8000)
--SET @ENDS=RIGHT(@ENDS,LEN(@ENDS)-CHARINDEX(',',@ENDS))
SET @START=SUBSTRING(@START,CHARINDEX(',',@START)+1,8000)
--SET @START=RIGHT(@START,LEN(@START)-CHARINDEX(',',@START))
SET @EDATE=SUBSTRING(@EDATE,CHARINDEX(',',@EDATE)+1,8000)
--SET @EDATE=RIGHT(@EDATE,LEN(@EDATE)-CHARINDEX(',',@EDATE))
SET @EYEAR=SUBSTRING(@EYEAR,CHARINDEX(',',@EYEAR)+1,8000)
--SET @EYEAR=RIGHT(@EYEAR,LEN(@EYEAR)-CHARINDEX(',',@EYEAR))
SET @NUMS=CHARINDEX(',',@START)
END
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
GO
