用户自定义存储过程的创建

T-SQL语法

CREATE PROC[EDURE] 存储过程名 [{@参数 数据类型} [= 默认值] [OUTPUT]], ......, {@参数 数据类型} [= 默认值] [OUTPUT] ] AS SQL 语句

 

不带参数的存储过程

查看本次考试的平均分以及未通过考试的学员名单

/*不带参数的存储过程*/ use stuDB go if exists(select * from sysobjects where name='proc_stu') drop procedure proc_stu go create procedure proc_stu as declare @writtenAvg float,@labAvg float select @writtenAvg=AVG(writtenExam),@labAvg=AVG(LabExam) from stuMark print '笔试平均分：'+convert(varchar(5),@writtenAvg) print '机试平均分：'+convert(varchar(5),@labAvg) if(@writtenAvg>70 and @labAvg>70) print '本班考试成绩：优秀' else print '本班考试成绩：较差' print '---------------------------' print ' 参加本次考试没有通过的学员' select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMark on stuInfo.stuNo=stuMark.stuNo where writtenExam<60 or LabExam<60 go exec proc_stu

 

执行结果

 

 

带输入参数的存储过程

输入参数可以在调用时向存储过程传递参数，此类参数可以用来在存储过程中传入值。

实例：由于考试难度不同，及格线也可能随时变化。我们添加两个输入参数，分别表示及格分数和机试及格分数

/*带输入参数的存储过程*/ use stuDB go if exists(select * from sysobjects where name='proc_stu') drop procedure proc_stu go create procedure proc_stu @writtenPass int=60, @labPass int=60 as print '笔试及格线：'+convert(varchar(5),@writtenPass) +'机试及格线：'+convert(varchar(5),@labPass) declare @writtenAvg float,@labAvg float select @writtenAvg=AVG(writtenExam),@labAvg=AVG(LabExam) from stuMark print '笔试平均分：'+convert(varchar(5),@writtenAvg) print '机试平均分：'+convert(varchar(5),@labAvg) if(@writtenAvg>70 and @labAvg>70) print '本班考试成绩：优秀' else print '本班考试成绩：较差' print '---------------------------' print ' 参加本次考试没有通过的学员' select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMark on stuInfo.stuNo=stuMark.stuNo where writtenExam<@labPass or LabExam<@labPass go --调用存储过程 exec proc_stu --都采用默认值60 exec proc_stu 64 --笔试64，机试默认值60 exec proc_stu 60,55 --笔试默认值，机试55 --或者这样调用：exec proc_stu @labPass==55,@writtenPass=60

 

执行结果

 

 

带输出参数的存储过程

输出参数：输出参数后面有OUTPUT标记，执行存储过程后，将把返回值存放在输出参数中。

实例：希望返回未通过考试的学员人数。

/*带输出参数的存储过程*/ use stuDB go if exists(select * from sysobjects where name='proc_stu') drop procedure proc_stu go create procedure proc_stu @notPassSum int output, @writtenPass int=60, @labPass int=60 as print '笔试及格线：'+convert(varchar(5),@writtenPass) +'机试及格线：'+convert(varchar(5),@labPass) declare @writtenAvg float,@labAvg float select @writtenAvg=AVG(writtenExam),@labAvg=AVG(LabExam) from stuMark print '笔试平均分：'+convert(varchar(5),@writtenAvg) print '机试平均分：'+convert(varchar(5),@labAvg) if(@writtenAvg>70 and @labAvg>70) print '本班考试成绩：优秀' else print '本班考试成绩：较差' print '---------------------------' print ' 参加本次考试没有通过的学员' select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMark on stuInfo.stuNo=stuMark.stuNo where writtenExam<@labPass or LabExam<@labPass select @notPassSum=COUNT(stuNo) from StuMark where writtenExam<@labPass or LabExam<@labPass go --调用存储过程 declare @sum int --定义变量，用于存放调用存储过程时返回的结果 exec proc_stu @sum output print '-----------ss-------------------------' if (@sum >=3) print '未通过人数：'+convert(varchar(5),@sum)+'人，超过60%,及格分数线应该下调' else print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线居中' go

 

执行结果：

 

处理错误信息

RAISERROR返回用户定义的错误时，可以指定严重级别，设置系统变量记录所发生的错误。

RAISERROR语法如下：

RAISERROR ({msg_id | msg_str}{, severity, state} [WITH option[,...n]])

1.msg_id:在sysmessages系统表中指定的用户定义错误信息

2.msg_str：用户定义的特定信息，最长255个字符

3.severity：与特定信息相关联，表示用户定义的严重级别。用户可以使用级别的0~18级。19~25级是为sysadmin固定角色的成员预留的，并且需要指定WITH LOG选项。20~25被认为是致命的错误。

4.state：表示错误的状态，是1~127的值。

5.option：指示是否将错误记录到服务器错误日志中。

实例：当用户传入的及格线不再0~100之间时，将弹出错误警告，终止存储过程的执行。 

 

/*处理错误信息*/ use stuDB go if exists(select * from sysobjects where name='proc_stu') drop procedure proc_stu go create procedure proc_stu @notPassSum int output, @writtenPass int=60, @labPass int=60 as /*-------------------------------错误处理----------------------------------*/ if(not @writtenPass between 0 and 100) or (not @labPass between 0 and 100) begin raiserror('及格线错误，统计中断退出',16,1) return end print '笔试及格线：'+convert(varchar(5),@writtenPass) +'机试及格线：'+convert(varchar(5),@labPass) declare @writtenAvg float,@labAvg float select @writtenAvg=AVG(writtenExam),@labAvg=AVG(LabExam) from stuMark print '笔试平均分：'+convert(varchar(5),@writtenAvg) print '机试平均分：'+convert(varchar(5),@labAvg) if(@writtenAvg>70 and @labAvg>70) print '本班考试成绩：优秀' else print '本班考试成绩：较差' print '---------------------------' print ' 参加本次考试没有通过的学员' select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMark on stuInfo.stuNo=stuMark.stuNo where writtenExam<@labPass or LabExam<@labPass select @notPassSum=COUNT(stuNo) from StuMark where writtenExam<@labPass or LabExam<@labPass go --调用存储过程 declare @sum int,@t int exec proc_stu @sum output,600 set @t=@@ERROR print '错误号：'+convert(varchar(5),@t) if @t<>0 return print '------------------------------------' if (@sum >=3) print '未通过人数：'+convert(varchar(5),@sum)+'人，超过60%,及格分数线应该下调' else print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线居中' go

执行结果：