--创建存储过程的例子
CREATE PROCEDURE proc_stu
AS
DECLARE @writtenAvg float,@labAvg numeric(6,2)--定义变量
SELECT @writtenAvg=AVG(written),
@labAvg=AVG(lab) FROM Exam
print '笔试平均分:'+convert(varchar(6),@writtenAvg)
print '机试平均分:'+convert(varchar(6),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本校考试成绩:优秀'
ELSE
print '本校考试成绩:较差'
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员:'
SELECT stuName,stuInfo.stuNo,written,lab
FROM stuInfo INNER JOIN exam ON
stuInfo.stuNo=exam.stuNo
WHERE written<60 OR lab<60
GO