------------------------------------------------------------------------------------------------------------------------------------------------------
自己寫的存儲過程：
use test
go

if exists (select name from sysobjects where name='v_order' and type='p')
DROP PROCEDURE v_order
go

create procedure v_order
?@serverid varchar(30),
?@jtype?? int
?as
?select * from
?order_form
where jtype= @jtype and serverid=@serverid

select * from sysobjects where type='p' and name='v_order'

exec v_order 'hcair','3'
go
exec v_order hcair,3

exec v_order @serverid=hcair,@jtype=3

drop proc v_order_in
go
create procedure v_order_in
@user_name varchar(20),
@pnr_list? varchar(20),
@air_list? varchar(20),
@arri_list? varchar(20),
@dp_time??? timestamp,
@lxname?? varchar(20)
as
insert into order_form(user_name,pnr_list,air_list,arri_list,dp_time,lxname) values (@user_name,@pnr_list,@air_list,@arri_list,@dp_time,@lxname )
go

exec v_order_in 0000,rksts,MF8587,'KHN,PEK,',convert('2006-7-16 13:03:53'),linsanfu
-------------------------------------------------------------------------------------------------------------------------------------------------------
示例
A. 使用帶有復雜 SELECT 語句的簡單過程
下面的存儲過程從四個表的聯接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
???????? WHERE name = 'au_info_all' AND type = 'P')
?? DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
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
GO

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

EXECUTE au_info_all
-- Or
EXEC au_info_all

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

au_info_all

B. 使用帶有參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數精確匹配的值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
???????? WHERE name = 'au_info' AND type = 'P')
?? DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE 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 = @lastname
GO

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

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

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

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

C. 使用帶有通配符參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數進行模式匹配，如果沒有提供參數，則使用預設的默認值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'au_info2' AND type = 'P')
?? DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
?? @lastname varchar(30) = 'D%',
?? @firstname varchar(18) = '%'
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 LIKE @firstname
?? AND au_lname LIKE @lastname
GO

au_info2 存儲過程可以用多種組合執行。下面只列出了部分組合：

EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

D. 使用 OUTPUT 參數
OUTPUT 參數允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執行期間設置的某個值。下面的示例創建一個存儲過程 (titles_sum)，并使用一個可選的輸入參數和一個輸出參數。

首先，創建過程：

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
????? WHERE name = 'titles_sum' AND type = 'P')
?? DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

接下來，將該 OUTPUT 參數用于控制流語言。

?

說明? OUTPUT 變量必須在創建表和使用該變量時都進行定義。

參數名和變量名不一定要匹配，不過數據類型和參數位置必須匹配（除非使用 @@SUM = variable 形式）。

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
?? PRINT ' '
?? PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
?? SELECT 'The total cost of these titles is $'
???????? + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

下面是結果集：

Title Name??????????????????????????????????????????????????????????????
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The 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 pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

接下來，執行一個批處理，聲明一個局部游標變量，執行上述過程以將游標賦值給局部變量，然后從該游標提取行。

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
?? FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

F. 使用 WITH RECOMPILE 選項
如果為過程提供的參數不是典型的參數，并且新的執行計劃不應高速緩存或存儲在內存中，WITH RECOMPILE 子句會很有幫助。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'titles_by_author' AND type = 'P')
?? DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
?? title AS Title
FROM authors a INNER JOIN titleauthor ta
?? ON a.au_id = ta.au_id INNER JOIN titles t
?? ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO

G. 使用 WITH ENCRYPTION 選項
WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創建加密過程，使用 sp_helptext 系統存儲過程獲取關于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關于該過程的信息。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'encrypt_this' AND type = 'P')
?? DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

EXEC sp_helptext encrypt_this

下面是結果集：

The object's comments have been encrypted.

接下來，選擇加密存儲過程內容的標識號和文本。

SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
?? ON c.id = o.id
WHERE o.name = 'encrypt_this'

下面是結果集：

?

說明? text 列的輸出顯示在單獨一行中。執行時，該信息將與 id 列信息出現在同一行中。

id???????? text???????????????????????????????????????????????????????
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

H. 創建用戶定義的系統存儲過程
下面的示例創建一個過程，顯示表名以 emp 開頭的所有表及其對應的索引。如果沒有指定參數，該過程將返回表名以 sys 開頭的所有表（及索引）。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'sp_showindexes' AND type = 'P')
?? DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
?? @@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
?? i.name AS INDEX_NAME,
?? indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
?? ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO????????
USE pubs
EXEC sp_showindexes 'emp%'
GO

下面是結果集：

TABLE_NAME?????? INDEX_NAME?????? INDEX_ID
---------------- ---------------- ----------------
employee???????? employee_ind???? 1
employee???????? PK_emp_id??????? 2

(2 row(s) affected)

I. 使用延遲名稱解析
下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時不存在，但每個存儲過程都可創建。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'proc1' AND type = 'P')
?? DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
?? SELECT *
?? FROM does_not_exist
GO?
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
?? ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'proc2' AND type = 'P')
?? DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
?? DECLARE @middle_init char(1)
?? SET @middle_init = NULL
?? SELECT au_id, middle_initial = @middle_init
?? FROM authors
GO?
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
?? ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'

?