1、13 13.2創過 13 13.2創過 CREATE PROCEDURE dName,LastNameEXECUTE- 執行系過程EXECUTEp_who; - 雖然指定了架構，但是仍然執行sys.sp_who系過CREATE PROCEDURE HumanResourSELECTLastName,Name,JobTitle,Department FROM HumanResourEXECUTE - 使用參數默認值，將搜索姓氏以字母D開頭的EXECUTECREATE PROCEDURE HumanResourlastname varchar(40) = D%,namevarchar(20) =S

2、ELECTLastName,Name,JobTitle,Department FROM HumanResourName LIKE ANDLastNameLIKEStandardCost money = 0, SELECTFROMWHEREStandardCostStandardCostANDListPrice ListPricemoneySELECTFROMWHEREStandardCostStandardCostANDListPrice StandardCostmoney, SETProductCount(SELECTFROM Production.Product WHEREListPric

3、eListPrice);- 搜索姓氏以字母Wi開頭的EXECUTE - 下面的語句將使用name參數的默認值，搜索姓氏以字母D開頭的EXECUTE name=- 搜索姓氏以C或K 開頭，并且在n前是O或E字母，中間ars的姓EXECUTE.usp_GetEmployees2- 搜索姓氏為HesseStefen的EXECUTE.usp_GetEmployees2Hesse,- 搜索姓氏以字母H開頭、名字以S開頭的EXECUTE.usp_GetEmployees2H%,一個cursor一個cursor類型的變量，并使用OUTPUT關鍵字將它修過ROCEDUREProduction.usp_GetP

4、roduct StandardCostmoney, SELECTDECLAREMyCursorcursor - 定義變EXECUTEProduction.usp_GetProduct100,10,MyCursorOUTPUT - 執IFUS(variableMyCursor0 - 判斷游標是否正FETCHNEXTFROMWHILEUS=0) -FETCHNEXTFROMMyCursor; CLOSE MyCursor; SETList_CursorCURSORLOCALSCROLLFOR -局部可滾動游標 SELECTName,FROMWHEREStandardCostStandardCost

5、ANDListPriceListPrice; OPEN list_cursor -打開游標過程的重新編過程的錯誤過程的重新編過程的錯誤處StandardCostmoney,ListPricemoney SELECTFROMWHEREStandardCostStandardCostANDListPriceListPriceStandardCostmoney, SELECTFROMWHEREStandardCostStandardCostANDListPriceListPrice FROMWHEREStandardCostStandardCostANDListPrice 1RAISERROR 1R

6、AISERROR IFOBJECT_ID(usp_GetErrorInfo,P)ISNOTNULL DROP PROCEDURE usp_GetErrorInfo; SELECT*FROM sys.messages WHEREmessage_id=21;- 另一個BEGINSELECTERROR_NUMBER()ASErrorNumber; END CATCH;ProductNumber varchar(20)IF(ProductNumber=ST-140)OR(ProductNumber=ST-141) ：%s。,12,1,SELECTFROMWHEREProductNumber=Produ

7、ctNumber - 檢IFOBJECT_ID(Nusp_RethrowError,NP)ISNOTNULL DROP PROCEDURE usp_RethrowError;- 創過程，使用RAISERROR生成一個錯誤- 原始錯誤信息用于構建RAISERRORmsg_str CREATE PROCEDURE usp_RethrowError AS- 如果沒有檢索到錯誤信息，直接返IFERROR_NUMBER()ISNULL - 創建 ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, E() as ErrorS

8、e, ERROR_PROCEDURE()asErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage;- 生成除以0錯SELECT1/0; ENDTRY BEGIN CATCH- 執過EXECUTEusp_GetErrorInfo; END CATCH;ErrorS e ,- RAISERROR捕獲ErrorNumber = ERROR_NUMBER(), ErrorSeverity=ERROR_SEVERITY(),ErrorS e ,- RAISERROR捕獲ErrorNumber = ERROR_

9、NUMBER(), ErrorSeverity=ERROR_SEVERITY(),ErrorS e=ErrorLine= - 構建包含原始錯誤信息的字符 N錯誤%d, 級別%d, %d, %s, %d, N信息: + ERROR_MESSAGE();-一個錯誤：RAISERROR的msg_str參數將包含原始錯誤信(ErrorS e,- 參數: 原始錯- 參數: 原始錯誤嚴重級- 參數: 原始錯誤狀ErrorProcedure, - 參數: 原始錯誤過程名- 參數: 原始錯誤行- 檢IFOBJECT_ID(Nusp_GenerateError,NP)ISNOTNULL DROP PROCEDU

10、RE usp_GenerateError;13.313.3.1 創建一個13.313.3.1 創建一個具有輸出參數的過- 創- 聯的CATCH塊捕獲，并且會因為被執行- - 而不是生成錯誤消息- 表中存在一個外鍵約束，下面的語句將生成一DELETEFROMProduction.Product WHERE ProductID = 980;ENDTRY - 調EXECusp_RethrowError; ENDCATCH;- 在下面的批中，在usp_GenerateError中發生的錯誤將激- - 在- 下面調用批中的外部CATCHRY -outer-Calltheproceduretogenera

11、teanerror. EXECUTEusp_GenerateError;ENDBEGINCATCH - CATCHERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE()asErrorMessage;END 過程的代碼，用于計算 過程的代碼，用于計算Sales.SalesOrderDetail10using System; usingSystem.Data;using System.Data.SqlC nt; using System.Data.SqlTypes; publicpartialclasspublics icvoid OrderQtySum(ou

12、t Sql 16value) /定義輸出參數，類型為/設置連SqlConnection conn = new SqlConnection(); conn.ConnectionString=ContextConnection=true; /設置命mand cmd = newcmd.Connection = conn;mandText=SELECTTOP10OrderQtyFROMusing System; usingSystem.Data;using System.Data.SqlC nt; using System.Data.SqlTypes; publicpartialclasspublic

13、 s ic void OrderQtySum()/ 在此處放置代2使用SQL13.3.2 創建返回行2使用SQL13.3.2 創建返回行集和信息的過using System; usingSystem.Data;using System.Data.SqlC nt; using System.Data.SqlTypes; publicpartialclassWITH PERMIS_SET=SAFE; - 創建基于程序集過value smallWITHEXECUTEAS CALLER - 指定執行權EXTERNALNAMEMyClrStoredProc.StoredProcedures.OrderQ

14、tySum; - 指定要使用的方法名SQLServer中的數據，并累value =SqlDataReaderreader=whilevalue16(0); /獲取指定列的值（0表示第1列，依次類推using System; usingSystem.Data;using System.Data.SqlC nt; using System.Data.SqlTypes; using System; usingSystem.Data;using System.Data.SqlC nt; using System.Data.SqlTypes; publicpartialclasspublics icvo

15、id SendReaderToC nt(Sql 32 /設置連SqlConnection conn = new SqlConnection(); conn.ConnectionString=ContextConnection=true; /設置命publics icvoid ExecuteToC nt(Sql 32/設置連SqlConnection conn = new SqlConnection(); conn.ConnectionString=ContextConnection=true; /設置命mand cmd = newcmd.Connection = conn;mandText=S

16、ELECTProductID,OrderQty,UnitPriceFROMSales.SalesOrderDetail+ WHERE SalesOrderID = OrderID;cmd.Parameters.AddWithValue(OrderIDOrderID); /設置cmd所使用的參數的SqlContext.Pipe.ExecuteAndSend(cmd); /傳遞到客戶 13.3.3 刪除過程和程 13.3.3 刪除過程和程13.4 IFOBJECT_IDroc,NP)ISNOTusing System; usingSystem.Data;using System.Data.SqlC

17、 nt; using System.Data.SqlTypes; publicpartialclasspublic s ic void Sayoworld!Itsnow+System.DateTime.Now.ToString()+mand cmd = newcmd.Connection = conn;mandText=SELECTProductID,OrderQty,UnitPriceFROMSales.SalesOrderDetail+ WHERE SalesOrderID = OrderID;cmd.Parameters.AddWithValue(OrderIDOrderID); /設置cmd所使用的參數的SqlDataReaderrcmd.ExecuteReader(); /生成SqlDataReader SqlContext.Pipe.Send(r); /傳遞到客戶端下面的示例則演示了使用SELECT、EXECsp_executesql下面的示例則演示了使用SELECT、EXECsp_executes