SQL自定义函数

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

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

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

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

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)

 

转载于:https://www.cnblogs.com/xiashengwang/p/3508779.html