Stored Procedure

◎ 建立一個可以接收參數的 Stored Procedure

IF EXISTS( SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_TYPE = 'PROCEDURE' AND SPECIFIC_NAME = '欲查詢的PROCEDURE名稱'
          )
BEGIN
    DROP PROCEDURE [欲刪除的PROCEDURE名稱]
END
GO

CREATE PROCEDURE [PROCEDURE名稱]-- 名稱盡量不要取「sp_」or「xp_」or「ms_」因為是系統的保留字，盡量取「spADDRESS」、「spCITY」，意即「sp+NAME」
(
    @PARA1 INT,
    @PARA2 INT

)
AS
    BEGIN
        SELECT *
        FROM TABLE A
        WHERE A.Column_1 = @PARA1 AND A.Column_2 = @PARA2
    END
GO
EXEC [PROCEDURE名稱] 1, 2--呼叫時，傳入對應的參數之數量&型別
--也可以用以下2種方式執行 Stored Procedure
--EXEC [PROCEDURE名稱] @PARA1=1, @PARA2=2
--EXEC [PROCEDURE名稱] @PARA2=2, @PARA1=1

◎ 建立一個可以"動態"接收參數的 Stored Procedure ( 建議使用 )

CREATE PROCEDURE spSearchGamer3
    @FirstName NVARCHAR(100) = NULL ,
    @LastName NVARCHAR(100) = NULL ,
    @Gender NVARCHAR(50) = NULL ,
    @GameScoreGreaterThanOrEqual INT = NULL
AS
    BEGIN
        DECLARE @sqlParams NVARCHAR(MAX) = N'@FN NVARCHAR(100), @LN NVARCHAR(100), @Gen NVARCHAR(50), @Gsgtoe INT';
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = 'SELECT * FROM Gamer WHERE 1 = 1';
        IF ( @FirstName IS NOT NULL )
            SET @sql = @sql + ' AND FirstName LIKE ''%''+@FN+''%''';
        IF ( @LastName IS NOT NULL )
            SET @sql = @sql + ' AND LastName LIKE ''%''+@LN+''%''';
        IF ( @Gender IS NOT NULL )
            SET @sql = @sql + ' AND Gender=@Gen';
        IF ( @GameScoreGreaterThanOrEqual IS NOT NULL )
            SET @sql = @sql + ' AND GameScore>=@Gsgtoe';
        EXECUTE sp_executesql @sql, @sqlParams, @FN = @FirstName,
            @LN = @LastName, @Gen = @Gender,
            @Gsgtoe = @GameScoreGreaterThanOrEqual;
    END;
GO

◎ 撈取DataBase中所有的 Stored Procedure(找FUNCTION也適用)

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

◎ 建立一個可以「OUTPUT」參數的 Stored Procedure

--注意：帶入的參數要設定長度，若沒有設定長度，預設長度為1，如果執行SP的時候帶入的參數長度超過1，SSMS只會截取第一碼當參數帶入
CREATE PROCEDURE [spEmployeeGender]
(
    @PARA1 VARCHAR(5),--參數設定長度為5，長度可以設超過TABLE欄位的長度沒關係，預防發生錯誤
    @Count INT OUTPUT--這是OUTPUT值
)
AS
    BEGIN
        SELECT @Count = COUNT(*)
        FROM Employees
        WHERE TitleOfCourtesy = @PARA1
    END
GO

DECLARE @PrintCount INT
DECLARE @execSTATUS_CODE INT

EXEC @execSTATUS_CODE = [spEmployeeGender] 'Mr.',--傳入參數'Mr.'
                        @PrintCount OUTPUT;
--也可以用以下2種方式執行 Stored Procedure
--EXEC @execSTATUS = [spEmployeeGender] @PARA1 = 'Mr.', @Count = @PrintCount OUTPUT;
--EXEC @execSTATUS = [spEmployeeGender] @Count = @PrintCount OUTPUT, @PARA1 = 'Mr.';

IF @PrintCount IS NOT NULL
    BEGIN
        PRINT 'STATUS CODE = ' + CONVERT(VARCHAR,@execSTATUS) + ' 代表執行成功，TitleOfCourtesy = Mr. 共有 ' + CONVERT(VARCHAR,@PrintCount) + ' 筆資料！'
    END
ELSE
    BEGIN
        PRINT 'STATUS CODE = ' + CONVERT(VARCHAR,@execSTATUS) + ' 代表執行失敗，共有 0 筆資料！'
    END
GO

◎ 建立一個可以「RETURN」參數的 Stored Procedure

--注意：這種方式只能RETURN型態為INT的值，其餘像是DATE、VARCHAR...等等的都不能RETURN，所以一般建議使用「OUTPUT」方式的Stored Procedure即可
CREATE PROCEDURE [spTEST]
AS
    BEGIN
        RETURN
        (
            SELECT COUNT(*)
            FROM [Categories]
        )
    END
GO

DECLARE @Count INT = 0
EXEC @Count = spTEST
PRINT '共有' + CONVERT(VARCHAR,@Count) + '筆資料！'

◎ Stored Procedure using USER DEFINE TABLE TYPE PARAMETER

CREATE TABLE PersonA
(
  Id INT PRIMARY KEY ,
  [Name] NVARCHAR(100) ,
  Gender NVARCHAR(10)
);

CREATE TYPE PersonType AS TABLE
(
    Id INT PRIMARY KEY,
    [Name] NVARCHAR(100),
    Gender NVARCHAR(10)
);

CREATE PROCEDURE spInsertPersonA
    @PersonTableType PersonType READONLY
AS
    BEGIN
        INSERT  INTO PersonA
                SELECT  *
                FROM    @PersonTableType;
    END;


DECLARE @PersonTableType2 PersonType;
INSERT  INTO @PersonTableType2 VALUES  ( 'Name01', 'Male' );
INSERT  INTO @PersonTableType2 VALUES  ( 'Name02', 'Female' );
INSERT  INTO @PersonTableType2 VALUES  ( 'Name03', 'Female' );
INSERT  INTO @PersonTableType2 VALUES  ( 'Name04', 'Male' );
INSERT  INTO @PersonTableType2 VALUES  ( 'Name05', 'Male' );
EXECUTE spInsertPersonA @PersonTableType2;

◎ 修改 Stored Procedure

ALTER PROCEDURE [PROCEDURE名稱]
(
    @PARA1 INT,
    @PARA2 INT

)
AS
    BEGIN
        SELECT *
        FROM TABLE A
        WHERE A.Column_1 = @PARA1 AND A.Column_2 = @PARA2
    END
GO

◎ 查詢 Stored Procedute 的 Script

sp_HELPTEXT [Stored Procedute Name]

◎ 其餘快速鍵/小技巧

sp_HELP [TableName]/[Stored Procedure Name]

或

在SQL指令部分，游標選取 [TableName]/[Stored Procedure Name] 之後，Alt + F1 可以查看所有的相關資訊

◎ Stored Procedure 跟 FUNCTION 的差別

1.FUNCTION 可以用在 SELECT 裡面，而 Stored Procedure 不可以

例如：SELECT sp_City <-- 這是錯的
EXEC sp_City <-- 這是對的
2.如果FUNCTION回傳的是一個TABLE(Inline Table Valued Function, ITVF)，則還可以JOIN其他TABLE