建立Stored Procedure
在這一節中，您可以瞭解如何在SQL資料庫中透過建立、執行和偵錯Stored Procedure來達到Procedural Data Integrity。首先您要先瞭解如何利用Visual Basic的Data View增益功能來為資料庫建立新的Stored Procedure；接著您會學到撰寫帶參數的Stored Procedure，用以傳入參數和傳回參數給原呼叫程式；最後，您會學到如何在Visual Basic中利用T-SQL Debugger對一個Stored Procedure進行偵錯。
以Data View建立Stored Procedure
如果您在Visual Basic專案上加入一個Data Environment，並和SQL Server建立資料庫連結，那麼您便可以透過Data View視窗來建立一個Stored Procedure。
4利用Data View來建立一個新的Stored Procedure
- 在Data View視窗中，在 Stored Procedure 目錄上按下滑鼠右鍵，然後選取 New 。
- 在New Stored Procedure視窗中，建立一個Stored Procedure並指定一個名稱。
- Data View便會建立一個包含 Create Procedure 的Stored Procedure範本，依需要加入適當的參數或者是加入 Select 指令到Stored Procedure。
- 在New Stored Procedure視窗按右鍵，儲存這個Stored Procedure。
- 關閉New Stored Procedure視窗。
Create Procedure陳述式
Stored Procedure是透過Create Procedure陳述式建立的，其語法為：
CREATE PROCEDURE [owner.]procedure_name[;number]
[(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
[{[WITH] | [,]} ENCRYPTION]]
AS sql_statements
以下便是Stored Procedure常用的參數：
參數
說明
procedure_name
對Stored Procedure指定一個新的名稱，名稱必須是在這個資料庫和擁有者下唯一的名稱，而且名稱總長度不可以超過20個字元。
parameter
指定Stored Procedure中的參數，您可以在Create Procedure敘述當中宣告一或多個參數。使用者必須在Stored Procedure執行時提供每一個參數的數值(除非這些參數有預設值)。一個Stored Procedure最多可以有255個參數。
sql_statements
指示這一個Stored Procedure所要進行的動作。 如果想要進一步瞭解Create Procedure語法中其他參數的意義，您可以在Visual Basic輔助說明中以Create Procedure statement為關鍵字搜尋相關資訊。
以下的範例會建立一個名為count_loanhist的Stored Procedure：
CREATE PROC count_loanhist
AS
SELECT COUNT(*) FROM loanhist
注意：Create Procedure不可以和其他SQL陳述式存在於同一個batch中。
在Stored Procedure加入參數
使用者可以透過參數傳遞資訊給Stored Procedure，您當然也可以藉由參數讓Stored Procedure執行完畢之後，傳回資訊給使用者。
您對每一個Stored Procedure最多可以宣告255個參數，每一個參數定義的語法如下：
parameter = @parameter_name datatype [=default][Output]
下表說明定義一個參數時，參數定義的語法裡所使用的引數：
|
引數 |
說明 |
|
parameter_name |
指定參數的名稱，不過在名稱的前頭要加入@符號。 |
|
datatype |
指定參數的資料型別，除了Image之外所有型別都可以使用。 |
|
default |
指定參數的預設值，假如有設定預設值的話，使用者可以不需要輸入數值給參數便可以執行Stored Procedure。 |
|
Output |
表示這一個參數是一個傳回資訊的參數。這個選項可以讓這個參數值傳回給呼叫Stored Procedure的 Execute 陳述式。將傳回資訊透過這一個參數送回給原呼叫程序。 |
以下便是一個建立一個名為mathtutor的Stored Procedure的範例程式，這一個Stored Procedure的功能是計算兩個輸入參數@m1和@m2的乘積，此外這兩個參數的資料型別設定為smallint，而Output參數@result則會傳回計算後的結果。
CREATE PROCEDURE mathtutor
@m1 smallint,
@m2 smallint,
@result smallint OUTPUT
AS
SELECT @result = @m1 * @m2
下列程式碼是定義一個名為 Customer_ListByLastName 的Stored Procedure，但是此Procedure是傳回一整組資料而不是單一的 Output 參數。
CREATE PROCEDURE Customer_ListByLastName
@vLastName varchar(30)
AS
BEGIN
DECLARE @vLast varchar(31)
SELECT @vLast = @vLastName + '%'
SELECT Lastname + ', ' + FirstName as FullName,
LastName, FirstName, Address, City, State, PostalCode, Country,
PhoneNumber, Balance, Email, CustomerID from Customers
WHERE LastName LIKE @vLast ORDER BY FULLNAME
END
@vLast參數是由輸入參數@vLastName加上萬用字元「%」，而查詢結果是利用SELECT指令在WHERE敘述當中以@vLast為條件搜尋LastName得來的結果。
注意：Declare可以在您的SQL程式碼中定義一個變數 。
對Stored Procedure進行偵錯
當您在安裝Visual Basic時，Visual Basic T-SQL Debugger便會自動地安裝在企業版工具裡。透過Visual Basic T-SQL Debugger，您可以在Visual Basic發展環境中直接對撰寫在SQL內的Stored Procedure進行偵錯。
4在Visual Basic中使用T-SQL Debugger
- 在 Add-Ins 功能表中選取 Add-In Manager 。
- 在可以選取的增益功能當中，點選 VB T-SQL Debugger ，並按下 OK 按鈕。
- 選取 Load/Unload 核取方塊，把T-SQL Debugger加入 Add-Ins 功能表中。
- 在 Add-Ins 功能表中選取 T-SQL Debugger ，這時 Visual Basic Batch T-SQL Debugger 對話方塊便會出現。
- 在 Settings 頁籤中，要填完連結所需的設定，以便連結Stored Procedure所在的SQL Server資料庫。
- 在 Stored Procedure 頁籤中，選取您想要偵錯的Stored Procedure，假如需要參數的話，請分別對每一個參數填入數值。
- 按下 Execute 按鈕，此時T-SQL Debugger便會出現。
您也可以利用下列的方法來啟動T-SQL Debugger：
- 利用Data Environment Designer。
- 在Data View視窗中對某一個Stored Procedure按下滑鼠右鍵，並在快顯功能表中選取 Debug 指令。
- 利用UserConnection Designer。
透過T-SQL Debugger，您可以：
- 顯示SQL呼叫堆疊(stack)、區域變數和Stored Procedure中的參數。
- 控制和管理Breakpoint。
- 檢視區域變數和參數。
- 檢視全域變數。
對Visual Basic程式碼進行偵錯時，您同時可以用T-SQL Debugger作執行時期的偵錯。若是要設定T-SQL Debugger作執行時期的偵錯，您可以在 Tools 功能表中選取 T-SQL Debugging Options ，接著選取 自動地透過RDO and ADO連線 ， 逐步追蹤至預存程序中 核取方塊。當您逐步追蹤至任何執行Stored Procedure的ADO程式碼時，便可以利用T-SQL Debugger進入Stored Procedure內進行偵錯。
注意：若是以SystemAccount的身份登入SQL Server，T-SQL Debugger無法運作。要修改此一選項，可以從Control Panel的 Services 中，按兩下 MSSQLServer 服務。假如這一項服務是以 SystemAccount 執行的話，那請變更為另一個可以登入伺服器的使用者名稱 。
留言列表
