表:student
表:teacher
列:TEACHER_ID,NAME,AGE,SEX,SALARY,STUDENT_NUM
需求:将性别和年龄符合给定条件的学生,都指派给指定的老师,并将学生的数量,更新到teacher表中。
业务成功,则返回学生的数量,否则返回-1。
存储过程的代码:
CREATE PROCEDURE UPDATE_TEACHER_TABLE(@P_S_SEX VARCHAR(5),@P_AGE INT,@P_T_ID VARCHAR(10),@P_RESULT INT OUTPUT)AS
BEGIN
DECLARE @adv_rowcount INT
DECLARE @adv_error INT
SELECT @NUM=COUNT(STUDENT_ID) FROM student WHERE SEX=@P_S_SEX AND AGE=@P_AGE
UPDATE teacher SET STUDENT_NUM=@NUM WHERE TEACHER_ID=@P_T_ID
SELECT @adv_error = @@ERROR, @adv_rowcount=@@ROWCOUNT
IF @adv_error != 0 OR @adv_rowcount = 0
BEGIN