TransactSQL selectCourse storedprocedurestuSysInfo project form cmm

选课存储过程业务逻辑
course 表和student 表,student_course表
student不能重复选课
student选课时间（timeRange字段）不能有冲突
上代码：
View Code
 1 CREATE PROCEDURE [dbo].[selectCourse](
 2 @course_id int ,
 3 @student_id int ,
 4 @result int output
 5 )
 6 AS
 7 BEGIN
 8 SET NOCOUNT ON;
 9     IF(EXISTS(SELECT * FROM [student_course] WHERE [course_id] = @course_id AND [student_id] = @student_id))
10     BEGIN
11     set @result = 0;
12     --RETURN 12;--已经选择了该课程
13     END
14     ELSE IF(EXISTS(SELECT * FROM [student_course] WHERE [student_id] = @student_id))
15     BEGIN
16         --print 'ss'
17         declare @courseid int,@timerange int,@timerangetoinsert int;    
18         SELECT @timerangetoinsert=timeRange FROM [course] where id = @course_id;
19         --print @timerangetoinsert;
20         DECLARE selectedcourse_cursor CURSOR FOR
21         select sc.course_id,c.timeRange from [student_course] as sc inner join course as c on c.id = sc.course_id where [student_id] = @student_id;
22         
23         OPEN selectedcourse_cursor;
24         
25         -- Perform the first fetch and store the values in variables.
26         -- Note: The variables are in the same order as the columns
27         -- in the SELECT statement. 
28 
29         FETCH NEXT FROM selectedcourse_cursor
30         INTO @courseid,@timerange;
31 
32         -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
33         WHILE @@FETCH_STATUS = 0
34         BEGIN
35             IF(@timerangetoinsert<>@timerange)
36                 BEGIN
37                 INSERT INTO [student_course](course_id,student_id)VALUES(@course_id,@student_id);
38                 set @result = 1;--选课成功
39                 BREAK;--选课成功
40                 END
41             ELSE 
42             BEGIN
43                 set @result = 2;--时间冲突
44                 BREAK;
45             END
46 
47         -- This is executed as long as the previous fetch succeeds.
48         FETCH NEXT FROM selectedcourse_cursor
49         INTO @courseid,@timerange;
50         END
51 
52         CLOSE selectedcourse_cursor;
53         DEALLOCATE selectedcourse_cursor;
54     END
55     ELSE 
56     BEGIN
57         INSERT INTO [student_course](course_id,student_id)VALUES(@course_id,@student_id);
58         set @result = 1;--选课成功
59     END
60 RETURN @result;
61 END