CREATE FUNCTION [dbo].[Round2]

(

    -- Add the parameters for the function here

    @p1 sql_variant,

    -- decimal numbers

    @scale int

)

RETURNS sql_variant

AS

BEGIN

    -- 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 @Result

END

-- 注意，前缀dbo好像不能省略，不清楚原因

select dbo.round2(123.456,2)

drop function round2

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

)

AS

BEGIN

    -- 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

END

GO

select * from f1(1,1)

CREATE FUNCTION f2

(

    -- Add the parameters for the function here

    @p1 int,

    @p2 char

)

RETURNS TABLE

AS

RETURN

(

    -- Add the SELECT statement with parameter references here

    SELECT top 10 * from dbo.DQuestionData

)

GO

select * from  f2(1,1)