SQL Server 預存程序(stored procedure) 示範

基礎

建立最簡單的 預存程序(stored procedure)，名稱為 myTestProcedure ：

    
CREATE OR ALTER PROCEDURE myTestProcedure
AS
BEGIN
    SELECT * FROM Users;
END
GO
    

執行名稱為 myTestProcedure 的預存程序：

    
EXEC myTestProcedure;
    

刪除名稱為 myTestProcedure 的預存程序：

    
DROP PROCEDURE myTestProcedure;
    

預存程序位於 SSMS 內資料庫 > 可程式性 > 預存程序：

傳入參數

定義有傳入參數的預存程序：

    
CREATE OR ALTER PROCEDURE myTestProcedure(
    @id nvarchar(450)
)
AS
BEGIN
    SELECT * FROM Users where Id = @id
END
GO
    

帶參數呼叫：

    
EXEC myTestProcedure 'AAA';
    

也可以指定具體參數名稱：

    
EXEC myTestProcedure  @id = 'AAA';
    

也可以定義多個參數：

    
CREATE OR ALTER PROCEDURE myTestProcedure(
    @id nvarchar(450),
    @name nvarchar(450)
)
AS
BEGIN
    SELECT * FROM Users where Id = @id or UserName = @name
END
GO
    

參數預設值

定義參數後如果執行預存程序時沒有傳入參數會拋出以下錯誤：

    
[S0004][201] Line 0: Procedure or function 'myTestProcedure' expects parameter '@id', which was not supplied.
    

但是如果有些參數不是必要的不需要傳入可以怎麼做？可以透過指定預設值就可以變成非必填：

    
CREATE OR ALTER PROCEDURE myTestProcedure(
    @id nvarchar(450) = NULL
)
AS
BEGIN
    IF @id IS NULL
        RETURN; -- 提前返回

    SELECT * FROM Users where Id = @id
END
GO
    

區域變數

如果只有單個查詢通常不會這麼費工的去定義預存程序，預存程序通常都是拿來做複雜的運算，那區域變數該如何宣告和賦值呢？使用示範：

    
CREATE OR ALTER PROCEDURE myTestProcedure(
    @name nvarchar(450)
)
AS
BEGIN
    DECLARE @UserId nvarchar(100); -- 宣告變數
    SET @UserId = (SELECT Id FROM Users where UserName = @name) -- 變數賦值

    PRINT 'USER_ID: ' + @UserId -- 印出變數
    SELECT * FROM Departments WHERE Creator = @UserId; -- 使用變數查詢
END
GO
    

延伸閱讀：
Microsoft SQL Server 在 select 中的 if else 替代方式示範(iif, case when)

參考資料：
Microsoft.Learn - Stored procedures (Database Engine)
Microsoft.Learn - Create a stored procedure
Microsoft.Learn - Execute a stored procedure
Microsoft.Learn - Delete a stored procedure