下载
加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 584332 TryCatch errata

584332 TryCatch errata.doc

584332 TryCatch errata

zhao9998
2018-09-08 0人阅读 举报 0 0 暂无简介

简介:本文档为《584332 TryCatch erratadoc》,可适用于IT/计算机领域

ThefollowingtextshouldbeplacedinBeginningSQLServerProgrammingonprightbeforethesectionentitled“HandlingErrorsBeforeTheyHappen”TRYCATCHBlocksOk,sowe’veseenthewaythatthingsweredoneundertheolderstyleofsocallederrorhandlingItwasandissomethinglessthanelegant,butitwaswhatwehadThetoughpartisthatwestillneedtolimitourselvestoconstantcheckingofERRORifweneedbackwardcompatibilityatallThegoodnewsisthat,ifyouareaSQLServeronlyshop,youhaveamoretraditionalerrorhandleroptionintheformofaTRYCATCHBlockATRYCATCHblockinSQLServerworksremarkablysimilartothoseusedinanyCderivedlanguage(C,C,C#,Delphi,andahostofothers)Thesyntaxlookslikethis:BEGINTRY     {<sqlstatement(s)>}ENDTRYBEGINCATCH     {<sqlstatement(s)>}ENDCATCHInshort,SQLServerwill“try”torunanythingwithintheBEGINENDthatgoeswithyourTRYblockIf,andonlyif,youhaveanerrorconditionthathasanerrorlevelof(we’lldiscusserrorlevelsabitlater)occurs,thenSQLServerwillexittheTRYblockimmediatelyandbeginwiththefirstlineinyourCATCHblockNow,totestthisout,we’llmakesomealterationstoourspInsertDateValidatedOrderstoredprocedurefromourprevioussectionlookingattheolderstyleerrorhandlingInsteadofdoingspecificinlinetests,wearegoingtomovetheINSERTstatementintothemiddleofaTRYCATCHblockNow,anyerrorbetweenandwillraiseanerrorforuswithouthavingtoretestERRORourselves:……ELSEBEGINSELECTInsertedOrderDate=CONVERT(datetime,(CONVERT(varchar,OrderDate,)))PRINT'TheTimeofDayinOrderDatewastruncated'END*EstablishourTRYCATCHBlock*BEGINTRY*Createthenewrecord*INSERTINTOOrdersVALUES(CustomerID,EmployeeID,InsertedOrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)ENDTRYBEGINCATCHUhoh,somethingwentwrong,seeifit'ssomethingweknowwhattodowithDECLAREErrorNoint,Severitytinyint,Statesmallint,LineNoint,Messagenvarchar()SELECTErrorNo=ERRORNUMBER(),Severity=ERRORSEVERITY(),State=ERRORSTATE(),LineNo=ERRORLINE(),Message=ERRORMESSAGE()IFErrorNo=TheproblemisaconstraintviolationPrintoutsomeinformationalhelptosteertheusertothemostlikelyproblemBEGINPRINT'Supplieddataviolatesdataintegrityrules'PRINT'Checkthatthesuppliedcustomernumberexists'PRINT'inthesystemandtryagain'ENDELSEOops,it'ssomethingwehaven'tanticipated,tellthemthatwedon'tknow,printouttheerrorBEGINPRINT'AnunknownerroroccurredContactyourSystemAdministrator'PRINT'Theerrorwasnumber'CONVERT(varchar,Error)ENDRegardlessoftheerror,we'regoingtosenditbacktothecallingpieceofcodesoitcanbehandledatthatlevelifnecessaryRETURNErrorENDCATCH*Movetheidentityvaluefromthenewlyinsertedrecordintoouroutputvariable*SELECTOrderID=IDENTITYRETURNNotethat,forthesakeofbrevity,I’veclippedmostofthecodebeforeandafterthiscodeiftryingthisonasystematyourhomeorworkplace,besureandfitthehighlightedsectionproperlyamongthenonhighlitedsectionsbeforestartingthingsupNoticeIusedsomespecialfunctionstoretrievetheerrorcondition,solet’stakealookatthoseAlsonotethatImovedthemintovariablesthatwerecontrolledbymesotheywouldnotbelostFunctionReturnsERRORNUMBER()TheactualerrornumberIfthisisasystemerror,therewillbeaentryinthesysmessagestable(usesysmessagestolookitup)thatmatchestothaterrorandcontainssomeoftheinformationwe’llgetformtheothererrorrelatedfunctionsERRORSEVERITY()Thisequatestowhatissometimescalled“errorlevel”inotherpartsofthisbookandBooksOnlineMyapologiesfortheinconsistencyI’mguiltyofperpetuatingsomethingthatMicrosoftstarteddoingaversionortwoagoERRORSTATE()IusethisassomethingofaplacemarkThiswillalwaysbeforsystemerrorsWhenweexampleerrorhandlinginmoredepth,we’llseehowtoraiseourownerrorsAtthatpoint,youcanusestatetoindicatethingslikeatwhatpointinyourstoredprocedure,functionortriggertheerroroccurred(thishelpswithsituationswhereagivenerrorcanhandleinanyoneofmanyplaces)ERRORPROCEDURE()Wedidnotusethisintheprecedingexample,asitisonlyrelevanttostoredprocedures,functions,andtriggersThissuppliesthenameoftheprocedurethatcausedtheerrorveryhandyifyourproceduresarenestedatall,astheprocedurethatcausestheerrormaynotbetheonetoactuallyhandlethaterrorERRORLINE()JustwhatitsaysthelinenumberoftheerrorERRORMESSAGE()ThetextthatgoeswiththemessageForsystemmessages,thisisthesameaswhatyou’llseeifyouselectthemessagefromthesysmessagesfunctionForuserdefinederrors,itwillbethetextsuppliedtotheRAISERRORfunctionInourexample,IutilizedaknownerrorIDthatSQLServerraisesifweattempttocreateanobjectthatalreadyexistsYoucanseeallsystemerrormessagesbyselectingthemfromthesysmessagestablefunctionParticularlywithSQLServer,thesysmessagesoutputhasgottensolengthythatit’shardtofindwhatyou’relookingforbyjustscanningitMysolutionislessthanelegant,butisrathereffectiveIjustartificiallycreatetheerrorI’mlookingforandseewhaterrornumberitgivesme(simplesolutionsforsimplemindslikemine!)IsimplyexecutethecodeIwanttoexecute(inthiscase,theCREATEstatement)andhandletheerrorifthereisonetherereallyisn’tmuchmoretoitthanthat

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/3

584332 TryCatch errata

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利