建立並在項目中調用SQLSERVER預存程序的簡單樣本

標籤：條件   blank   有用   暫存資料表   編程   項目   vcs   簡化編程   with   

使用SQLSERVER預存程序可以很大的提高程式運行速度，簡化編程維護難度，現已得到廣泛應用。
建立預存程序 
　　和資料表一樣，在使用之前需要建立預存程序，它的簡明文法是： 

引用: 
Create PROC 預存程序名稱 
    [參數列表（多個以“,”分隔）] 
AS 
SQL 陳述式 

例： 

引用: 
Create PROC upGetUserName 
@intUserId        INT, 
@ostrUserName NVARCHAR(20) OUTPUT                -- 要輸出的參數 
AS 
BEGIN 
        -- 將uName的值賦給 @ostrUserName 變數，即要輸出的參數 
        Select @ostrUserName=uName FROM uUser Where [email protected] 
END 

　　其中 Create PROC 語句（完整語句為Create PROCEDURE）的意思就是告訴SQL SERVER，現在需要建立一個預存程序，upGetUserName 就是預存程序名稱，@intUserId 和 @ostrUserName 分別是該預存程序的兩個參數，注意，在SQL SERVER中，所有使用者定義的變數都以“@”開頭，OUTPUT關鍵字表示這個參數是用來輸出的，AS之後就是預存程序內容了。只要將以上代碼在“查詢分析器”裡執行一次，SQL SERVER就會在當前資料庫中建立一個名為“upGetUserName”的預存程序。你可以開啟“企業管理器”，選擇當前操作的資料庫，然後在左邊的樹型列表中選擇“預存程序”，此時就可以在右邊的列表中看到你剛剛建立的預存程序了（如果沒有，重新整理一下即可）。 
二、預存程序的調用 

　　之前已經建立了一個名為“upGetUserName”的預存程序，從字面理解該預存程序的功能是用來取得某一個使用者的名稱。預存程序建立好了，接下來就是要在應用程式裡調用了，下面看一下在ASP程式裡的調用。 

引用: 
Dim adoComm 

’// 建立一個對象，我們用來調用預存程序 
Set adoComm = CreateObject("ADODB.Command") 
With adoComm 
        ’// 設定串連，設 adoConn 為已經串連的 ADODB.Connection 對象 
        .ActiveConnection = adoConn 
        ’// 類型為預存程序，adCmdStoredProc = 4 
        .CommandType = 4 
        ’// 預存程序名稱 
        .CommandText = "upGetUserName" 
        ’// 設定使用者編號 
        .Parameters.Item("@intUserId").Value = 1 
        ’// 執行預存程序 
        .Execute 
         
        ’// 取得從預存程序返回的使用者名稱稱 
        Response.Write "使用者名稱：" & .Parameters.Item("@ostrUserName").Value 
End With 
’// 釋放對象 
Set adoComm = Nothing 

　　通過以上兩步，已經可以建立和使用簡單的預存程序了。下面來看一個稍微複雜點的預存程序，以進一步瞭解預存程序的應用。
三、預存程序的實際應用 

　　使用者登入在ASP項目中經常會使用到，但使用預存程序來做驗證可能不多，那麼做例子，寫一個簡單的使用者登入驗證的預存程序。 

引用: 
Create PROC upUserLogin 
@strLoginName        NVARCHAR(20), 
@strLoginPwd        NVARCHAR(20), 
@blnReturn                BIT OUTPUT 
AS 
-- 定義一個臨時用來儲存密碼的變數 
DECLARE @strPwd NVARCHAR(20) 
BEGIN 
        -- 從表中查詢目前使用者的密碼，賦值給 @strPwd 變數，下面要對他進行比較 
        Select @strPwd=uLoginPwd FROM uUser Where [email protected] 

        IF @strLoginPwd = @strPwd 
                BEGIN 
                        SET @blnReturn = 1 
                        -- 更新使用者最後登入時間 
                        Update uUser SET uLastLogin=GETDATE() Where [email protected]
                END 
        ELSE 
                SET @blnReturn = 0 
END 

　　使用者登入的預存程序建立好了。注意，在一個地區內如果有多條語句時，必需使用BEGIN...END關鍵字。
引用: 
Dim adoComm 
’// 建立一個對象，我們用來調用預存程序 
Set adoComm = CreateObject("ADODB.Command") 
With adoComm 
        ’// 設定串連，設 adoConn 為已經串連的 ADODB.Connection 對象 
        .ActiveConnection = adoConn 
        ’// 類型為預存程序，adCmdStoredProc = 4 
        .CommandType = 4 
        ’// 預存程序名稱 
        .CommandText = "upUserLogin" 
        ’// 設定登入名稱稱 
        .Parameters.Item("@strLoginName").Value = "***" 
        ’// 設定登入密碼 
        .Parameters.Item("@strLoginPwd").Value = "123456" 
        ’// 執行預存程序 
        .Execute 
         
        ’// 判斷是否登入成功 
        If .Parameters.Item("@blnReturn").Value = 1 Then 
                Response.Write "恭喜你，登入成功！" 
        Else 
                Response.Write "不是吧，好像錯了哦。。。" 
        End If 
End With 
’// 釋放對象 
Set adoComm = Nothing 

　　通過以上的步驟，簡單使用者登入驗證過程也做完了，現在只要把它整合到程式中就可以實現簡單的使用者登入驗證了，關於其他細節就由你自己來處理了。 
　　上面介紹的兩個預存程序都是只返回一個值的，下面我們來看一個返回一個記錄集的預存程序。 

引用: 
Create PROC upGetUserInfos 
@intUserGroup        INT 
AS 
BEGIN 
        -- 從資料庫中抽取合格資料 
        Select uName,uGroup,uLastLogin FROM uUser Where [email protected] 
        -- 插入一列合計 
        UNION 
        Select ’合計人數:’,COUNT(uGroup),NULL FROM uUser Where [email protected]
END 

　　現在我們來看一下ASP程式的調用。 

引用: 
Dim adoComm 
Dim adoRt 
’// 建立一個對象，我們用來調用預存程序 
Set adoComm = CreateObject("ADODB.Command") 
Set adoRs = CreateObject("ADODB.Recordset") 
With adoComm 
        ’// 設定串連，設 adoConn 為已經串連的 ADODB.Connection 對象 
        .ActiveConnection = adoConn 
        ’// 類型為預存程序，adCmdStoredProc = 4 
        .CommandType = 4 
        ’// 預存程序名稱 
        .CommandText = "upGetUserInfos" 
        ’// 設定使用者組 
        .Parameters.Item("@intUserGroup").Value = 1 
        ’// 執行預存程序，和以上幾個例子不同，這裡使用RecordSet的Open方法 
        adoRs.Open adoComm 
        ’// 顯示第一個值 
        Response.write adoRs.Fields(0).Value 
End With 
’// 釋放對象 
Set adoRs = Nothing 
Set adoComm = Nothing

最後讓我們看看 SQL SERVER裡面如何在預存程序裡面擷取另一個預存程序所返回的表的資料？

首先需要知道“另一個預存程序”的結果集的所有列的類型。 
假設“另一個預存程序”的名字是sp1，沒有參數，返回的結果集共3列，全部為int型，那麼“預存程序”裡添加一個與結果集列數相同的暫存資料表或表變數用於接收“另一個預存程序”的結果集
如下
CREATE PROCEDURE sp2
AS
DECLARE @t table(a int,b int,c int)

INSERT INTO @t(a,b,c)
EXEC sp1

SELECT * FROM @t

建立並在項目中調用SQLSERVER預存程序的簡單樣本