[SQL] Stored Procedure 預存程序的建立與刪除

stored procedure命名勿使用 sp_ 開頭(使用 usp_開頭)
因為使用sp_開頭的預設都會先至SQL Server的master database搜尋完後，才會搜尋現階段連線的database，不僅耗時更容易有機會出錯(比方跟master db存有的sp撞名)。

--建立SP
create procedure usp_test
as
    select getdate()

--執行sp
exec dbo.usp_test -- 加上 dbo. (schema name)可以省下搜尋時間

--修改sp
create procedure usp_test
as
    select getdate()+1

--刪除sp
drop procedure dbo.usp_test

--查看SP內容 (sp_helptext)
exec sp_helptext ups_GetSalesQty

兩節式命名：
相關的物件請都使用兩節式(schema name+object name)命名，
因為這可以直接且明確找到該物件和編譯過的執行計畫，
而省下搜尋其他schema底下可能的object所浪費的資源和時間。

建立預存程序 (Create Procedure)

/*創建預存程序語法: CREATE PROCEDURE {程序名稱}*/
CREATE PROCEDURE procedure_name /*注意: 名稱不能是sp_開頭!("sp_"是預留給系統的))*/
    /*如果要傳入變數的話，從這邊寫，屬性的型別要記得傳入*/
AS
BEGIN --Begin/End 可用可不用，只是讓程式碼看起來更明確
    /*從這邊開始輸入要預存的SQL指令*/
    sql_statement
END
GO;

注意: 名稱不能是sp_開頭!(“sp_“是預留給系統的)
使用usp_開頭

BEGIN/END 這二個關鍵字用來包住程式碼的區塊，可用可不用，只是讓程式碼看起來更明確。

執行預存程序 (Exec SP)

EXEC procedure_name;

刪除預存程序 (Drop Procedure)

drop procedure procedure_name

修改預存程序 (Alter Procedure)

alter procedure procedure_name
as
/*從這邊開始輸入SQL指令*/
    sql_statement

查看預存程序內容 (sp_helptext)

exec sp_helptext procedure_name

範例

範例1：無參數

The following SQL statement creates a stored procedure that selects Customers from a particular City from the “Customers” table:
(建立一個名為「SelectAllCustomers」的預存程序，該程序會從「Customers」表中選取所有記錄：)

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

執行上面的SP

exec SelectAllCustomers

刪除上面的SP

drop procedure SelectAllCustomers

範例2：一個參數

Stored Procedure With One Parameter(具有一個參數的預存程序)

The following SQL statement creates a stored procedure that selects Customers from a particular City from the “Customers” table: (建立一個預存程序，從「Customers」表中選擇特定城市的客戶：)

CREATE PROCEDURE SelectAllCustomers
    @City nvarchar(30)
AS
    SELECT * FROM Customers WHERE City = @City
GO;

執行上面的 procedure：

exec SelectAllCustomers @City = 'London';
--或是這樣寫:(省略變數名)
--exec SelectAllCustomers 'London'

範例3：多個參數

Stored Procedure With Multiple Parameters(具有多個參數的預存程序)

設定多個參數非常容易。只需列出每個參數和資料類型，並用逗號分隔，如下所示。

建立一個預存程序，從「Customers」表中選擇具有特定郵遞區號的特定城市的客戶：

CREATE PROCEDURE SelectAllCustomers 
    @City nvarchar(30), @PostalCode nvarchar(10)
AS
    SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

執行上面的SP：

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

範例4：一個輸入參數，一個輸出參數output

建立一個stored procedure，統計特定商品id的販賣數量(所取得的結果要放到參數中)
(輸入商品id，回傳統計的販賣數量)

--建立sp
create procedure usp_Test
    @id int,
    @qty int output --輸出參數要加上output
as
begin --begin/end 可用可不用，只是讓程式碼看起來更明確
    select sum(數量) as "販賣數量"
    from 販賣資料
    where 商品ID = @id
end

執行SP：

--執行sp
declare @qty int --宣告變數
exec dbo.usp_Test 1, @qty output --執行sp
select @qty --顯示結果

/* 執行結果:
販賣數量
6
*/

OUTPUT 可以使用縮寫 OUT

[SQL] Stored Procedure 小細節 MSDN - stored-procedures/create-a-stored-procedure
w3schools - SQL Stored Procedures for SQL Server
vito-note - Stored Procedures