语法格式
CREATE FUNCTION <函数名称>
(
-- 函数的参数(可以多个)
<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS
<@表变量名> TABLE
(
-- 返回的虚拟表中的字段 字段名 类型
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>,
<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
-- 函数体,里面可以定义一些变量,总之最后返回的应该是个结果集
RETURN
END
GO
编写实例
CREATE function IncNum(@num varchar(100))
returns nvarchar(20)
as
--将指定串递增
--参数:@num 编号
begin
declare @s nvarchar(20),
@s1 nvarchar(20),
@left nvarchar(20),
@right nvarchar(20),
@nextnum nvarchar(50)
set @s=@num
set @nextnum=''
set @s1=''
set @left=''
set @right=''
while len(@s)>0
begin
set @s1=substring(@s,1,1)
if @s1 like '[0123456789'
set @right=@right+@s1
else
set @right=''
set @s=stuff(@s,1,1,'')
end
if @right=''
return ''
set @right=cast(cast(@right as int)+1 as nvarchar(20))
set @left=left(@num,len(@num)-len(@right))
set @nextnum=@left+@right
return @nextnum
end
调用方法
使用: select dbo.IncNum('A001S1')
结果: A001S2
返回表
第一种方式
直接返回:
CREATE FUNCTION dbo.GetReportData (@Date1 varchar(10), @Date2 varchar(10))
RETURNS TABLE AS RETURN
SELECT TOP 100 PERCENT * FROM t_r_d_ProductReport WHERE m_Date >= @Date1 AND m_Date <= @Date2
ORDER BY m_Sequence
优点:简单明了,无中间变量。
缺点:不能在返回过程中添加其它程序片段。
第二种方式
创建TABLE类型的变量,向其中插入数据,然后返回:
CREATE FUNCTION dbo.DayProductReportSum (@Date varchar(10))
RETURNS @table TABLE
(
m_PlanedSum int,
m_ActualSum int,
m_ExcessSum int,
m_Sequence tinyint
)
AS
BEGIN
DECLARE @firstDay varchar(10)
SET @firstDay = dbo.GetFirstDayOfTenDays(@Date)
INSERT @table
SELECT SUM(m_PlanedOutput), SUM(m_ActualOutput), SUM(m_ActualOutput) - SUM(m_PlanedOutput), m_Sequence
FROM dbo.GetReportData(@firstDay, @Date)
GROUP BY m_Sequence
RETURN
END
优点:可以添加其它程序片段,比如定义新的变量等。
缺点:有中间变量,稍复杂。
CREATE FUNCTION dbo.DayProductReportSum (@Date varchar(10))
RETURNS @table TABLE
(
m_PlanedSum int,
m_ActualSum int,
m_ExcessSum int,
m_Sequence tinyint
)
AS
BEGIN
DECLARE @firstDay varchar(10)
SET @firstDay = dbo.GetFirstDayOfTenDays(@Date)
INSERT @table
SELECT SUM(m_PlanedOutput), SUM(m_ActualOutput), SUM(m_ActualOutput) - SUM(m_PlanedOutput), m_Sequence
FROM dbo.GetReportData(@firstDay, @Date)
GROUP BY m_Sequence
RETURN
END
if判断
--创建用户自定义函数
/****** 对象: UserDefinedFunction [dbo].[MemberName] 脚本日期: 08/03/2009 11:18:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[MemberName](@member_Id varchar(32),@member_type char(1))
returns varchar(300)
as
begin
Declare @memberName varchar(300)
if @member_type='R'
begin
set @memberName=(select name_ from wf_org_role where id_=@member_Id)
end
else if @member_type='G'
begin
set @memberName=(select name_ from wf_org_group where id_=@member_Id)
end
else if @member_type= 'D'
begin
set @memberName=(select name_ from wf_org_department where id_=@member_Id)
end
else if @member_type= 'U'
begin
set @memberName=(select displayname_ from wf_org_user where id_=@member_Id)
end
return @memberName
end
--调用查询语句例子
select dbo.MemberName(member_id,member_type),workflow_id,member_id,knowledge_code from tb_wf_privgrant
注意:
如果你这样来调用:
select MemberName(member_id,member_type),workflow_id,member_id,knowledge_code from tb_wf_privgrant
可能会报:
消息 195,级别 15,状态 10,第 1 行
'MemberName' 不是可以识别的 内置函数名称。
解决方案:
在自定义函数前加上所有者,如:dbo.