Database Reference
In-Depth Information
Figure 7.8
Execute procedure.
For a given class id, the irst query in the procedure counts the number of students in a class
and assigns the number of students to the variable @Enrollment. he second query assigns the
capacity of the classroom used for the class to the variable @Capacity. he variable @Seats is
declared and assigned the diference between @Capacity and @Enrollment. If the value in @Seats
is
>
0, the number of available seats is printed on the screen. Otherwise, the message 'No seat is
available' is printed on the screen. he build-in function CONVERT is used to convert the integer
value in the variable @Seats to a string of characters.
To execute a procedure, you need to use the keyword EXEC. Figure 7.8 shows the result of the
execution for the stored procedure Enrollment.
7.3.2.2 Modifying and Deleting Stored Procedures
he code in Figure 7.7 can cause a problem. By the department rules, a class may use two class-
rooms. For example, the VB class uses a classroom for lectures and another classroom for lab work.
In such a case, the query in the following code:
DECLARE @Capacity INT
SET @Capacity
=
(SELECT Capacity
FROM CLASS_CLASSROOM C, CLASSROOM M
WHERE C.ClassID
=
@ClassID AND
C.ClassroomID
=
M.ClassroomID AND
C.BuildingID
=
M.BuildingID)
will select two capacity values that cannot be assigned to the single value variable @Capacity.
When passing the ClassID 1000 to the procedure, you will get an error message. You can ix the
problem by modifying the procedure with the keyword ALTER as shown in Figure 7.9 where the
above code is changed to
DECLARE @Capacity INT
SELECT @Capacity
=
MIN(Capacity)
FROM CLASS_CLASSROOM C, CLASSROOM M
WHERE C.ClassID
=
@ClassID AND
C.ClassroomID
=
M.ClassroomID AND
C.BuildingID
=
M.BuildingID
in which the built-in function MIN is used to select and return the smallest capacity to the scalar
variable @Capacity.
