asp調用mssql預存程序之完全樣本

標　　題：SQL SERVER資料庫開發之預存程序的應用
作　　者：栽培者
日　　期：2005-12-27
說　　明：由於個人能力有限，文章中難免會出現錯誤或遺漏的地方，敬請諒解！同時歡迎你指出，以便我能及時修改，以免誤導下一個看官。最後希望本文能給你帶來一定的協助。

序
　　可能有不少朋友使用SQL SERVER做開發也已經有段日子，但還沒有或者很少在項目中使用預存程序，或許有些朋友認為根本沒有必要使用預存程序等等。其實當你一個項目做完到了維護階段時，就會發現預存程序給我們帶來了好處了，修改方便，不能去改我們的應用程式，只需要改預存程序的內容，而且還可以使我們的程式速度得到提高。

 SQL SERVER 聯機叢書中的定義：
　　預存程序是儲存起來的可以接受和返回使用者提供的參數的 Transact-SQL 陳述式的集合。
　　可以建立一個過程供永久使用，或在一個會話中臨時使用（局部暫存處理序），或在所有會話中臨時使用（全域暫存處理序）。
　　也可以建立在 Microsoft SQL Server 啟動時自動啟動並執行預存程序。
　　要使用預存程序，首先我們必需熟悉一些基本的T-SQL語句，因為預存程序是由於一組T-SQL語句構成的，並且，我們需要瞭解一些關於函數、過程的概念，因為我們需要在應用程式中調用預存程序，就像我們調用應用程式的函數一樣，不過調用的方法有些不同。

　　下面我們來看一下預存程序的建立和使用方法。

一、建立預存程序

　　和資料表一樣，在使用之前我們需要建立預存程序，它的簡明文法是：

 

以下是引用片段： CREATE PROC 預存程序名稱     [參數列表（多個以“,”分隔）] AS SQL 語句

例：

 

以下是引用片段： CREATE PROC upGetUserName @intUserId        INT, @ostrUserName NVARCHAR(20) OUTPUT                -- 要輸出的參數 AS BEGIN         -- 將uName的值賦給 @ostrUserName 變數，即要輸出的參數         SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId 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 uLoginName=@strLoginName         IF @strLoginPwd = @strPwd                 BEGIN                         SET @blnReturn = 1                         -- 更新使用者最後登入時間                         UPDATE uUser SET uLastLogin=GETDATE() WHERE uLoginName=@strLoginName                 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 = "admin"         ’// 設定登入密碼         .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 uGroup=@intUserGroup         -- 插入一列合計         UNION         SELECT ’合計人數:’,COUNT(uGroup),NULL FROM uUser WHERE uGroup=@intUserGroup 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

 　　怎麼樣，是不是也很簡單呢，不過預存程序的用處不僅僅只有這些，他還有更強大的功能，比如使用遊標、暫存資料表來從多個表，甚至是多個資料庫中調用資料，然後返回給使用者，這些你可以在使用過程中慢慢的去發掘。

　　好了，預存程序應用就先介紹到這裡，如果你對本文有任何疑問，歡迎跟貼提出，我將儘力給予解答。下次我將給大家介紹觸發器的應用。