Store procedure doesn't work for web application.

[zhengmao]

I have one store procedure, which works fine in SQL Analyzer when I test it out, but doesn't work when I call it in web application. the problem is it doesn't insert last line, and the problem is that one Select statement doesn't execute, does anyone have a clue for this?

IF @CourseList <> '' AND @EmployeeID <> 'DELETE'
BEGIN
SELECT @npos1Course = 1
SELECT @npos1RBY = 1
SELECT @nLoop = 1
SELECT @nBreak = 0

WHILE (1 = 1)
BEGIN
SELECT @npos2Course = ISNULL(charindex(',', @CourseList, @npos1Course), 0)
SELECT @npos2RBY = ISNULL(charindex(',', @ReqdDate, @npos1RBY), 0)

IF @npos2Course = 0
BEGIN
SELECT @npos2Course = LEN(@CourseList)
SELECT @nBreak = 1
END
IF @npos2RBY = 0
BEGIN
SELECT @npos2RBY = LEN(@ReqdDate)
SELECT @nBreak = 1
END

SELECT @nLengthCourse = @npos2Course - @npos1Course
SELECT @nLengthRBY = @npos2RBY - @npos1RBY

IF @nBreak = 1
BEGIN
SELECT @nLengthCourse = @nLengthCourse + 1
SELECT @nLengthRBY = @nLengthRBY + 1
END

IF @nLengthCourse > 0
BEGIN
SELECT @nValueCourse = SUBSTRING(@CourseList, @npos1Course, @nLengthCourse)
IF @nlengthRBY > 0
SELECT @nValueRBY = SUBSTRING(@ReqdDate, @npos1RBY, @nLengthRBY)


SELECT @CourseID = a.CourseID
FROM Courses a INNER JOIN OrgCourses b ON a.CourseID = b.CourseID
WHERE a.CourseCode = @nValueCourse AND b.OrgID = @OrgID

insert into debug values (@CourseID, @UserID, @OrgID, @nValueRBY,@npos1Course, @npos2Course)

-- BEGIN to add or delete user courses and grade book
IF RTRIM(LTRIM(@nValueRBY)) = 'DELETE' -- delete user course registration
BEGIN
SELECT @CourseID = a.CourseID
FROM Courses a INNER JOIN OrgCourses b ON a.CourseID = b.CourseID
WHERE a.CourseCode = @nValueCourse AND b.OrgID = @OrgID

DELETE
FROM UserCourses
WHERE UserID = @UserID
AND CourseID = @CourseID

DELETE
FROM GradeBook
WHERE UserID = @UserID
AND CourseID = @CourseID
END
ELSE
BEGIN

IF NOT EXISTS ( SELECT UserID FROM UserCourses WHERE CourseID = @CourseID AND UserID = @UserID)
BEGIN
IF Exists (Select CourseID from Courses Where CourseID = @CourseID)
BEGIN

IF @nLengthRBY > 0
BEGIN
INSERT INTO UserCourses(UserID, OrgID, CourseID, ExamSubmissions, ContentBookmark, ExamBookmark,
RequiredByDate, EmailDistribution, Instructor, LastLessonComplete, CreateDate,status)
VALUES
(@UserID, @OrgID, @CourseID, 0, 0, 0, CAST(RTRIM(LTRIM(@nValueRBY)) AS SMALLDATETIME), 0, 0, 0, getdate(),0)
END
ELSE
BEGIN
INSERT INTO UserCourses(UserID, OrgID, CourseID, ExamSubmissions, ContentBookmark, ExamBookmark,
RequiredByDate, EmailDistribution, Instructor, LastLessonComplete, CreateDate,status)
VALUES
(@UserID, @OrgID, @CourseID, 0, 0, 0, NULL, 0, 0, 0, getdate(),0)
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT -2 Status -- failed to add User Course entry
RETURN
END

INSERT INTO GradeBook
(CourseID, UserID, OrgID, Grade, Status)
VALUES
(@CourseID, @UserID, @OrgID, 0, 1)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT -1 Status -- error adding log-on account
RETURN
END
END
END
END
END

IF @nBreak = 1
BREAK

SELECT @npos1Course = @npos2Course + 1
SELECT @npos1RBY = @npos2RBY + 1
SELECT @nLoop = @nLoop + 1
SELECT @CourseID = 0
END
END