Sql Server資料庫的預存程序

一、建立預存程序

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

 

三、預存程序的實際應用

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關鍵字。

QUOTE:
// 建立一個對象，我們用來調用預存程序
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

通過以上的步驟，簡單使用者登入驗證過程也做完了，現在只要把它整合到程式中就可以實現簡單的使用者登入驗證了，關於其他細節就由你自己來處理了。

上面介紹的兩個預存程序都是只返回一個值的，下面我們來看一個返回一個記錄集的預存程序。

QUOTE:
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程式的調用。
QUOTE:
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