預存程序_摘要

A. 使用帶有複雜 SELECT 語句的簡單過程

下面的預存程序從四個表的聯結中返回所有作者（提供了姓名）、出版的書籍以及出版社。該預存程序不使用任何參數。

USE pubsIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info_all' AND type = 'P')   DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name   FROM authors a INNER JOIN titleauthor ta      ON a.au_id = ta.au_id INNER JOIN titles t      ON t.title_id = ta.title_id INNER JOIN publishers p      ON t.pub_id = p.pub_idGO

au_info_all 預存程序可以通過以下方法執行：

EXECUTE au_info_all-- OrEXEC au_info_all

如果該過程是批處理中的第一條語句，則可使用：

au_info_all

B. 使用帶有參數的簡單過程

下面的預存程序從四個表的聯結中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該預存程序接受與傳遞的參數精確匹配的值。

USE pubsIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info' AND type = 'P')   DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info    @lastname varchar(40),    @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name   FROM authors a INNER JOIN titleauthor ta      ON a.au_id = ta.au_id INNER JOIN titles t      ON t.title_id = ta.title_id INNER JOIN publishers p      ON t.pub_id = p.pub_id   WHERE  au_fname = @firstname      AND au_lname = @lastnameGO

au_info 預存程序可以通過以下方法執行：

EXECUTE au_info 'Dull', 'Ann'-- OrEXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'-- OrEXEC au_info 'Dull', 'Ann'-- OrEXEC au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXEC au_info @firstname = 'Ann', @lastname = 'Dull'

如果該過程是批處理中的第一條語句，則可使用：

au_info 'Dull', 'Ann'-- Orau_info @lastname = 'Dull', @firstname = 'Ann'-- Orau_info @firstname = 'Ann', @lastname = 'Dull'

C. 使用帶有萬用字元參數的簡單過程
下面的預存程序從四個表的聯結中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該預存程序對傳遞的參數進行模式比對，如果沒有提供參數，則使用預設的預設值。
USE pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'au_info2' AND type = 'P')   DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2   @lastname varchar(30) = 'D%',   @firstname varchar(18) = '%'AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta   ON a.au_id = ta.au_id INNER JOIN titles t   ON t.title_id = ta.title_id INNER JOIN publishers p   ON t.pub_id = p.pub_idWHERE au_fname LIKE @firstname   AND au_lname LIKE @lastnameGO
au_info2 預存程序可以用多種組合執行。下面只列出了部分組合：
EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%'-- OrEXECUTE au_info2 @firstname = 'A%'-- OrEXECUTE au_info2 '[CK]ars[OE]n'-- OrEXECUTE au_info2 'Hunter', 'Sheryl'-- OrEXECUTE au_info2 'H%', 'S%'D. 使用 OUTPUT 參數
OUTPUT 參數允許外部過程、批處理或多條 Transact-SQL 陳述式訪問在過程執行期間設定的某個值。下面的樣本建立一個預存程序 (titles_sum)，並使用一個可選的輸入參數和一個輸出參數。
首先，建立過程：
USE pubsGOIF EXISTS(SELECT name FROM sysobjects      WHERE name = 'titles_sum' AND type = 'P')   DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUTASSELECT 'Title Name' = titleFROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price)FROM titlesWHERE title LIKE @@TITLEGO
接下來，將該 OUTPUT 參數用於流程控制語言。 
說明  OUTPUT 變數必須在建立表和使用該變數時都進行定義。
參數名和變數名不一定要匹配，不過資料類型和參數位置必須匹配（除非使用 @@SUM = variable 形式）。 
DECLARE @@TOTALCOST moneyEXECUTE titles_sum 'The%', @@TOTALCOST OUTPUTIF @@TOTALCOST < 200 BEGIN   PRINT ' '   PRINT 'All of these titles can be purchased for less than $200.'ENDELSE   SELECT 'The total cost of these titles is $'          + RTRIM(CAST(@@TOTALCOST AS varchar(20)))
下面是結果集：
Title Name                                                               ------------------------------------------------------------------------ The Busy Executive's Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200.E. 使用 OUTPUT 遊標參數
OUTPUT 遊標參數用來將預存程序的局部遊標傳遞迴調用批處理、預存程序或觸發器。
首先，建立以下過程，在 titles 表上聲明並開啟一個遊標：
USE pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'titles_cursor' and type = 'P')DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUTASSET @titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM titlesOPEN @titles_cursorGO
接下來，執行一個批處理，聲明一個局部遊標變數，執行上述過程以將遊標賦值給局部變數，然後從該遊標提取行。
USE pubsGODECLARE @MyCursor CURSOREXEC titles_cursor @titles_cursor = @MyCursor OUTPUTWHILE (@@FETCH_STATUS = 0)BEGIN   FETCH NEXT FROM @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursorGO