Blog @ Blog

當 ODBC 應用程序與 SQL Server 連接時，服務器將自動設置會話的下列選項：

• SET QUOTED_IDENTIFIER ON
  
• SET TEXTSIZE 2147483647
  
• SET ANSI_DEFAULTS ON
  
• SET CURSOR_CLOSE_ON_COMMIT OFF
  
• SET IMPLICIT_TRANSACTIONS OFF

        這些設置將提高 ODBC 應用程序的可移植性。由于基于 DB-Library 的應用程序通常不設置這些選項，所以應在上述所列 SET 選項打開和關閉的情況下都對存儲過程進行測試。這樣可確保存儲過程始終能正確工作，而不管特定的連接在喚醒調(diào)用該存儲過程時可能設置的選項。需要特別設置其中一個選項的存儲過程，應在開始該存儲過程時發(fā)出一條 SET 語句。此 SET 語句將只對該存儲過程的執(zhí)行保持有效，當該存儲過程結(jié)束時，將恢復原設置。

示例

A. 創(chuàng)建使用參數(shù)的存儲過程

下例創(chuàng)建一個在 pubs 數(shù)據(jù)庫中很有用的存儲過程。給出一個作者的姓和名，該存儲過程將顯示該作者的每本書的標題和出版商。

CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

將出現(xiàn)一條說明該命令未返回任何數(shù)據(jù)也未返回任何行的消息，這表示已創(chuàng)建該存儲過程。

現(xiàn)在執(zhí)行 au_info 存儲過程：

EXECUTE au_info Ringer, Anne
GO

下面是結(jié)果集：

(2 row(s) affected)

B. 創(chuàng)建使用參數(shù)默認值的存儲過程

下例創(chuàng)建一個存儲過程 pub_info2，該存儲過程顯示作為參數(shù)給出的出版商所出版的某本書的作者姓名。如果未提供出版商的名稱，該存儲過程將顯示由 Algodata Infosystems 出版的書籍的作者。

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE @pubname = p.pub_name

執(zhí)行未指定參數(shù)的 pub_info2：

EXECUTE pub_info2
GO

下面是結(jié)果集：

(9 row(s) affected)

C. 執(zhí)行用顯式值替代參數(shù)默認值的存儲過程

在下例中，存儲過程 showind2 的 @table 參數(shù)默認值是 titles。

CREATE PROC showind2 @table varchar(30) = 'titles'
AS
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table

列標題（例如，TABLE_NAME）可使結(jié)果更具可讀性。下面是該存儲過程顯示的 authors 表的情況：

EXECUTE showind2 authors
GO

(2 row(s) affected)

如果用戶未提供值，則 SQL Server 將使用默認表 titles：

EXECUTE showind2
GO

下面是結(jié)果集：

(2 row(s) affected)

D. 使用參數(shù)默認值 NULL 創(chuàng)建存儲過程

參數(shù)默認值可以是 NULL 值。在這種情況下，如果未提供參數(shù)，則 SQL Server 將根據(jù)存儲過程的其它語句執(zhí)行存儲過程。不會顯示錯誤信息。

過程定義還可指定當不給出參數(shù)時要采取的其它某種措施。例如：

CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table

E. 使用包含通配符的參數(shù)默認值創(chuàng)建存儲過程

如果存儲過程將參數(shù)用于 LIKE 關鍵字，那么默認值可包括通配符（%、_、[] 和 [^]）。例如，可將 showind 修改為當不提供參數(shù)時顯示有關系統(tǒng)表的信息：

CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name LIKE @table

在存儲過程 au_info 的下列變化形式中，兩個參數(shù)都有帶通配符的默認值：

CREATE PROC au_info2 @lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
如果執(zhí)行 au_info2 時不指定參數(shù)，將顯示姓以字母 D 開頭的所有作者：

EXECUTE au_info2
GO

下面是結(jié)果集：

(3 row(s) affected)

下例在兩個參數(shù)的默認值已定義的情況下，省略了第二個參數(shù)，因此可找到姓為 Ringer 的所有作者的書和出版商：

EXECUTE au_info2 Ringer
GO

(4 row(s) affected)

posted on 2008-06-19 08:47 isabc 閱讀(4147) 評論(0)  編輯 收藏 引用 所屬分類: 數(shù)據(jù)庫