自定义函数分为：标量值函数或表值函数

如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。

如果 RETURNS 子句指定 TABLE，则函数为表值函数。

表值函数又可分为：内嵌表值函数（行内函数）或多语句函数

如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。

如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数。

标量值函数示例

CREATE FUNCTION dbo.Foo

(

)

RETURNS INT

AS

BEGIN

	DECLARE @n INT

	SELECT @n = 3

	RETURN @n

END 

内嵌表值函数示例

CREATE FUNCTION dbo.Foo

(

)

RETURNS TABLE

AS

	RETURN

	SELECT id,

	       title

	FROM   msgs 

多语句表值函数示例（部分）

CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)

RETURNS @retFindReports TABLE

(

    EmployeeID int primary key NOT NULL,

    Name nvarchar(255) NOT NULL,

    Title nvarchar(50) NOT NULL,

    EmployeeLevel int NOT NULL,

    Sort nvarchar (255) NOT NULL

)

--Returns a result set that lists all the employees who report to the

--specific employee directly or indirectly.*/

AS

BEGIN

   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS

    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),

        e.Title,

        e.EmployeeID,

        1,

        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)

     FROM HumanResources.Employee AS e

          JOIN Person.Contact AS c ON e.ContactID = c.ContactID

     WHERE e.EmployeeID = @InEmpID

   UNION ALL

     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +

        c.FirstName + ' ' + c.LastName),

        e.Title,

        e.EmployeeID,

        EmployeeLevel + 1,

        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

                 LastName)

     FROM HumanResources.Employee as e

          JOIN Person.Contact AS c ON e.ContactID = c.ContactID

          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

    )

-- copy the required columns to the result of the function

   INSERT @retFindReports

   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort

   FROM DirectReports

   RETURN

END;

GO

在以下示例中，调用了此函数。

-- Example invocation

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM dbo.fn_FindReports(109)

ORDER BY Sort;

注意其 RETURNS 部分。

多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。

赋值语句。

控制流语句。

DECLARE 语句，该语句定义函数局部的数据变量和游标。

SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。

游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。

INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。

EXECUTE 语句调用扩展存储过程。

ALTER FUNCTION [dbo].[AmountRole1]

(

    @Amount numeric(18,2)

)

RETURNS numeric(18,2)

AS

BEGIN

    DECLARE @i FLOAT

    IF @Amount >= 2000

        SET @i = @Amount * 0.98 --满2000打9.8折

    ELSE

        SET @i = @Amount

    RETURN (@i)

END 

自定义函数语法

Scalar Functions

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

    [ = default ] }

    [ ,...n ]

  ]

)

RETURNS return_data_type

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    BEGIN

                function_body

        RETURN scalar_expression

    END

[ ; ]

Inline Table-valued Functions

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

    [ = default ] }

    [ ,...n ]

  ]

)

RETURNS TABLE

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    RETURN [ ( ] select_stmt [ ) ]

[ ; ]

Multistatement Table-valued Functions

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

    [ = default ] }

    [ ,...n ]

  ]

)

RETURNS @return_variable TABLE < table_type_definition >

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    BEGIN

                function_body

        RETURN

    END

[ ; ]

CLR Functions

CREATE FUNCTION [ schema_name. ] function_name

( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type

        [ = default ] }

    [ ,...n ]

)

RETURNS { return_data_type | TABLE <clr_table_type_definition> }

    [ WITH <clr_function_option> [ ,...n ] ]

    [ AS ] EXTERNAL NAME <method_specifier>

[ ; ]

Method Specifier

<method_specifier>::=

    assembly_name.class_name.method_name

Function Options

<function_option>::=

{

    [ ENCRYPTION ]

  | [ SCHEMABINDING ]

  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

  | [ EXECUTE_AS_Clause ]

}

<clr_function_option>::=

}

    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

  | [ EXECUTE_AS_Clause ]

}

Table Type Definitions

<table_type_definition>:: =

( { <column_definition> <column_constraint>

  | <computed_column_definition> }

        [ <table_constraint> ] [ ,...n ]

) 

<clr_table_type_definition>::=

( { column_name data_type } [ ,...n ] )

<column_definition>::=

{

    { column_name data_type }

    [ [ DEFAULT constant_expression ]

      [ COLLATE collation_name ] | [ ROWGUIDCOL ]

    ]

    | [ IDENTITY [ (seed , increment ) ] ]

    [ <column_constraint> [ ...n ] ]

}

<column_constraint>::=

{

    [ NULL | NOT NULL ]

    { PRIMARY KEY | UNIQUE }

      [ CLUSTERED | NONCLUSTERED ]

        [ WITH FILLFACTOR = fillfactor

        | WITH ( < index_option > [ , ...n ] )

      [ ON { filegroup | "default" } ]

  | [ CHECK ( logical_expression ) ] [ ,...n ]

}

<computed_column_definition>::=

column_name AS computed_column_expression 

<table_constraint>::=

{

    { PRIMARY KEY | UNIQUE }

      [ CLUSTERED | NONCLUSTERED ]

            ( column_name [ ASC | DESC ] [ ,...n ] )

        [ WITH FILLFACTOR = fillfactor

        | WITH ( <index_option> [ , ...n ] )

  | [ CHECK ( logical_expression ) ] [ ,...n ]

}

<index_option>::=

{

    PAD_INDEX = { ON | OFF }   | FILLFACTOR = fillfactor

  | IGNORE_DUP_KEY = { ON | OFF }

  | STATISTICS_NORECOMPUTE = { ON | OFF }

  | ALLOW_ROW_LOCKS = { ON | OFF }

  | ALLOW_PAGE_LOCKS ={ ON | OFF }

}