SQL - Stored Procedure

Stored Procedure Intro

一般會將較複雜的運算放入 Stored Procedure 內運算
用法為 EXECUTE 或 EXEC

例如：

EXEC SchemaName.StoredProcedureName
	@parameter = 1;

Stored Procedure 傳參數時，參數名稱可以省略，所以也可以寫成

EXEC SchemaName.StoredProcedureName 1;

Passing Input Parameters to Stored Procedure

直接執行，回傳 SELECT statement

宣告

CREATE PROCEDURE SchemaName.StoredProcedureName
	(@parameter AS INT)
AS <Stored Procedure 的任務>

呼叫

EXEC SchemaName.StoredProcedureName 1;

Working with OUTPUT Parameter

與 Passing Input Parameters to Stored Procedure 的最大區別是，在需告的變數 @parameter 最後一個會有 OUTPUT

代表在做這個 Stored Procedure 的最後，會回傳一個值
因此在呼叫時，第一句需要先宣告一個變數 (和 Stored Procedure 最後一個參數名稱相同)，為了承接 OUTPUT 的回傳值

宣告

CREATE PROCEDURE SchemaName.StoredProcedureName
	(@parameterInput AS INT,
	 @parameterOutput AS INT OUTPUT)
AS <Stored Procedure 的任務>

呼叫
此時 SELECT @parameterOutput 結果會是 2

DECLARE @parameterOutput AS INT
EXEC SchemaName.StoredProcedureName 1, 2 OUTPUT;

SELECT @parameterOutput 

Creating Procedure to Return Rows

根據 CREATE Stored Procedure 中，AS 後的 Stored Procedure 的任務決定
Stored Procedure 的任務可以由多個 SELECT statement 組成，可以有多個回傳值

CREATE PROCEDURE SchemaName.StoredProcedureName
	(@parameterInput AS INT,
	 @parameterOutput AS INT OUTPUT)
AS <Stored Procedure 的任務>

通常 Stored Procedure 會利用 ALTER 來進行操作，如此一來便可以不用 DROP 或 RECREATE，以避免權限在轉移的過程中遺失

ALTER PROCEDURE statement

Creating Procedure That Accept Parameter

SchemaName.StoredProcedureName 中宣告的參數用於 SELECT statement 中
若有多個 SELECT statement，可使用 BEGIN END 包起來，可以提升程式碼觀看性

CREATE PROCEDURE SchemaName.StoredProcedureName
	(@parameter1 AS INT, @parameter2 AS INT)
AS
BEGIN
	SELECT TOP (@parameter1) ColumnName1, ColumnName2, ColumnName3
	FROM SchemaName.TableName
	WHERE ColumnName4 = @parameter2;
END

Dynamic SQL

Constructing Dynamic SQL

有兩種方式可以 Dynamic 的執行 SQL statement

EXEC 執行 SQL 字串
1
EXEC (N' <SELECT statement> ');

sp_executesql ()

DECLARE @SQLCode AS NVARCHAR(max) = 
	N' <SELECT statement> ';
EXEC sp_executesql @Statement = @SQLCode;