错误信息处理(存储过程)

错误信息处理为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）可在错误处理逻辑中检查下列错误：返回码、SQLServer错误、用户定义的错误信息RETURN语句从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）返回码为0表示成功。若用户不提供返回码，则返回SQLServer的返回码。用户定义的返回码优先级高于系统提供的返回码错误信息处理（续）CREATEPROCEDUREdbo.getorders@customeridnchar(10)ASselectorderid,customerid,employeeidfromorderswherecustomerid=@customeridreturn(@@rowcount)示例错误信息处理（续）sp_addmessage系统存储过程允许开发者创建用户定义错误信息，指定消息号、严重级别和消息文本，可在SYS.MESSAGES中查看execsp_addmessage@msgnum=50320,@severity=16,@msgtext='errorpass_score.',@with_log='true',@lang='us_english'@@error函数@@error系统函数包含了最近执行的Transact-SQL语句的错误号，随着每一条语句的执行而更新使用@@error系统函数来检测特定的错误号或有条件地退出存储过程RAISERROR语句返回用户定义的错误信息并设系统标志，记录发生的错误错误信息处理（续）RAISERROR(msg_id|msg_str,severity,stateWITHoption[,...n]])RAISERROR语句的用法如下：msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为0–18级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中错误信息处理（续）问题：当用户调用存储过程时，传入的及格线参数不在0～100之间时，将弹出错误警告，终止存储过程的执行。错误信息处理（续）CREATEPROCEDUREproc_stu@notpassSumintOUTPUT,--输出参数@writtenPassint=60,--默认参数放后@labPassint=60--默认参数放后ASIF(NOT@writtenPassBETWEEN0AND100)OR(NOT@labPassBETWEEN0AND100)BEGINRAISERROR('及格线错误，请指定0－100之间的分数，统计中断退出',16,1)RETURN---立即返回，退出存储过程END错误处理引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值错误信息处理（续）笔试及格线：默认为60分机试及格线：默认为60分SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam@writtenPassORlabExam@labPassSELECT@notpassSum=COUNT(stuNo)FROMstuMarksWHEREwrittenExam@writtenPassORlabExam@labPassGO错误信息处理（续）查询没有通过考试的学员统计并返回没有通过考试的学员人数/*---调用存储过程，测试RAISERROR语句----*/DECLARE@sumint,@tintEXECproc_stu@sumOUTPUT,604SET@t=@@ERRORprint'错误号：'+convert(varchar(5),@t)IF@t0RETURN--退出批处理，后续语句不再执行print'--------------------------------------------------'IF@sum=3print'未通过人数：'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'ELSEprint'未通过人数：'+convert(varchar(5),@sum)+'人,已控制在60%以下，及格分数线适中'GO笔试及格线误输入604分如果执行了RAISERROR语句，系统全局@@ERROR将不等于0，表示出现了错误错误信息处理（续）结构化异常处理的语法TRY···CATCH块提供结构TRY块包含受保护的事务CATCH块处理错误发生时执行事务CREATEPROCEDUREdbo.AddData@aint,@bintASBEGINTRYINSERTINTOTableWithKeyVALUES(@a,@b)ENDTRYBEGINCATCHSELECTERROR_NUMBER()ErrorNumber,ERROR_MESSAGE()[Message]ENDCATCH9.4.1结构化异常处理的语法处理错误的准则9.4.2处理错误的准则在TRY后立即创建CATCH块在CATCH块中回滚错误事务可以捕获错误信息使用XACT_ABORTON和XACT_STATE指定当Transact-SQL语句出现运行时错误时，SQLServer是否自动回滚到当前事务。指示会话是否具有活动事务以及是否可以提交事务。处理错误的准则9.4.2处理错误的准则SETXACT_ABORTONBEGINTRYBEGINTRAN...COMMITTRANENDTRYBEGINCATCHIF(XACT_STATE())=-1--uncommitableROLLBACKTRANELSEIF(XACT_STATE())=1--commitableCOMMITTRANENDCATCH练习1．首先自定义一个错误号为50512的用户自定义错误，错误的严重级别为10,错误的文本消息为’Can’tfindthecustomerID.’，另外消息中还要加上表名和输入的CustomerID,并且当发生消息时将消息写入Microsoft®WindowsNT®应用程序日志中。然后创建一个名为ExistCustomerID的存储过程，以用它来找出SQLSERVER中的northwind数据库的Customer表中，指定的CustomerID是否存在，如果存在返回0，如果不存在返回错误号50512，并将消息写入Microsoft®WindowsNT®应用程序日志中。写出定义错误消息和创建存储过程的语句。练习答案定义自定义错误消息EXECsp_addmessage@msgnum=50512,@severity=10,@msgtext='Can’tfindthecustomerID:%sattableCustomers',@with_log='true'创建存储过程的语句如下CREATEPROCExistCustomerID@CustomerIDchar(5)ASSELECTCustomerIDFromCustomersWHERECustomerID=@CustomerIDIF@@ROWCOUNT=0BEGINRAISERROR(50512,10,1,@CustomerID)RETURNENDRETURN0练习答案