USER DEFINED FUNCTION

◎ 建立一個 FUNCTION

CREATE FUNCTION fnTest()--括號內可以接收多個參數
RETURNS INT--一個FUNCTION只能回傳一個參數，型態可以是INT、VARCHAR、DATETIME...等等，更強的是可以回傳一個TABLE(Inline Table Valued Function, ITVF)
WITH SchemaBinding--一般建議加這一行，以防SELECT的TABLE被刪除，但TABLE前面一定要加SchemaName
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(*)
    FROM dbo.Employees
    RETURN @count;

    --也可以這樣寫
    --RETURN
    --(
    --    SELECT COUNT(*)
    --    FROM dbo.Employees
    --)
END

--呼叫FUNCTION
--前面一定要加SchemaName，通常SchemaName是dbo
SELECT [dbo].[fnTest]()--如果Function有定義接收的參數，括號內要傳對應的參數進去

◎ 建立一個可以回傳一個TABLE的FUNCTION

方法一：

--注意：帶入的參數要設定長度，若沒有設定長度，預設長度為1，如果執行SP的時候帶入的參數長度超過1，SSMS只會截取第一碼當參數帶入，帶入的參數設定長度時候可以超過WHERE欄位的長度沒關係
CREATE FUNCTION fnTest( @PARA1 NVARCHAR(30))--括號內可以接收多個參數
RETURNS TABLE--注意這裡是帶TABLE
WITH SchemaBinding--一般建議加這一行，以防SELECT的TABLE被刪除，但TABLE前面一定要加SchemaName
AS
RETURN
(
    SELECT *
    FROM dbo.Employees
    WHERE TitleOfCourtesy = @PARA1
)

方法二：

CREATE FUNCTION fnTEST()
RETURNS @Cacl TABLE
(
    Column_1 NVARCHAR(MAX),
    Column_2 NVARCHAR(MAX)
)
BEGIN

    INSERT @Cacl
    SELECT *
    FROM [OTHER_TABLE]

    RETURN;
END

--呼叫可以回傳TABLE的FUNCTION
SELECT *
FROM dbo.fnTest('Ms.')

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

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

◎ Stored Procedure 跟 FUNCTION 的差別

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

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