SQL自定义函数
2014-01-07 14:44 xiashengwang 阅读(378) 评论(0) 编辑 收藏 举报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)