1，自定义函数--返回单一值

CREATE FUNCTION [dbo].[Round2] (    -- Add the parameters for the function here    @p1 sql_variant,    -- decimal numbers    @scale int)RETURNS sql_variantASBEGIN    -- Declare the return variable here    DECLARE @Result sql_variant,@interval sql_variant    -- Add the T-SQL statements to compute the return value here    if @scale >=0         set @interval = 1.0/POWER(10,@scale) * 0.5    else        set @interval = POWER(10,@scale) * 0.5                if @p1 > @interval        set @Result = round(cast(@p1 as float) - cast( @interval as float),@scale)    else        set @Result = 0                -- Return the result of the function    RETURN @ResultEND

调用自定义函数

-- 注意，前缀dbo好像不能省略，不清楚原因select dbo.round2(123.456,2)

删除自定义函数

drop function round2

 2，自定义函数--返回一个表结构

2.1 返回的表结构自己定义

CREATE FUNCTION f1 (    -- Add the parameters for the function here    @p1 int,     @p2 char)RETURNS @Table_Var TABLE (    -- Add the column definitions for the TABLE variable here    c1 int,     c2 int)ASBEGIN    -- Fill the table variable with the rows for your result set    insert into @Table_Var values(1,2)    insert into @Table_Var values(1,2)    RETURN ENDGO

调用

select * from f1(1,1)

2.2 返回的表结构不明确定义，由select语句决定

CREATE FUNCTION f2(        -- Add the parameters for the function here    @p1 int,     @p2 char)RETURNS TABLE ASRETURN (    -- Add the SELECT statement with parameter references here    SELECT top 10 * from dbo.DQuestionData)GO

调用

select * from  f2(1,1)