|
写sql存储过程经常需要调用一些函数来使处理过程更加合理，也可以使函数复用性更强，不过在写sql函数的时候可能会发现，有些函数是在表值函数下写的有些是在标量值下写的，区别是表值函数只能返回一个表，标量值函数可以返回基类型。
举个例子，当用户删除一个节点的时候，是需要将当前节点下的所有子节点都删掉，如果程序只传一个当前节点，那就需要写一个函数来得到当前节点下的所有子节点，这些子节点的信息就可以放到一个表中返回。
ALTER FUNCTION testGetSubNodes
(
-- Add the parameters for the function here
@nodeId int
)
RETURNS
@t TABLE
(
-- Add the column definitions for the TABLE variable here
id bigint identity(1,1) not null,
nodeIds int ,
nodeName varchar(500)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
insert into @t values(@nodeId,'header');
while exists(
select nodeid from dbo.Tree where parentid
in (select nodeIds from @t) and nodeid not in(select nodeIds from @t))
begin
insert into @t select nodeid, nodename from dbo.Tree where parentid
in (select nodeIds from @t)
end
RETURN
END
这个函数的主要功能就是返回当前节点下的所有子节点，在存储过程中写
select * from testGetSubNodes(nodeId)就可以返回表中的数据了。
再写一个标量值函数
ALTER FUNCTION [dbo].[testGetSubNodes_]
(
@nodeId int
)
RETURNS int
AS
BEGIN
declare @nodeCount int
select @nodeCount=5 from MenuTree
return @nodeCount
END
这个函数很简单返回一个整型值，然后就可以在存储过程中调用了，不过调用的方式有所不同，象上面的表值函数调用是不需要所有者的，只要写函数名称就可以，对于标量值函数来说，是需要加上所有者的，比如所有者是dbo
select dbo.testGetSubNodes_,这样就可以返回5，如果不加dbo，那sql会不认识这个函数。
以下是本人写的例子
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <yuzt>
-- Create date: <2010-01-20 2:27 >
-- Type: 多语句表值函数
-- Description: <判断多次回录的时间是否在结算周期内.在周期内可以回录返回0,否则返回1>
-- 调用方法：SELECT * FROM dbo.ReadytestFunc('2010-2-28',1,107)
--SELECT * FROM dbo.F_IsOverFeedBackTime('2009-2-28',1,107)
-- =============================================
ALTER FUNCTION dbo.F_IsOverFeedBackTime
(
@applyorderTime DATETIME ,
@RegionID INT ,
@ModuleID INT
)
RETURNS @table TABLE (bsettle DATETIME,esettle DATETIME,returnval INT)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Ayear INT --订单提交时间年
DECLARE @Amonth INT --订单提交时间月
DECLARE @Aday INT --订单提交时间日
DECLARE @BEGINday INT --结算开始日
DECLARE @ENDday INT --结算截止日
DECLARE @BsettlementTime DATETIME; ----结算周期开始时间
DECLARE @EsettlementTime DATETIME; ----结算周期结束时间
DECLARE @NextYear INT ----下一年
DECLARE @PreYear INT ----上一年
DECLARE @NextMonth INT ----下一月
DECLARE @PreMonth INT ----上一月
DECLARE @returnval INT --返回值
SET @Ayear = YEAR(@applyorderTime)
SET @Amonth = MONTH(@applyorderTime)
SET @Aday = DAY(@applyorderTime)
SELECT @BEGINday = BeginDate ,@ENDday = ENDDate FROM Config_BillingCycle WHERE ModuleID=@ModuleID AND RegionID=@RegionID AND isvalid=1
IF(@Aday<=@BEGINday AND @Aday<=@BEGINday ) --上月和本月(都小于)
BEGIN
SET @PreMonth =@Amonth-1
IF(@PreMonth<=0)---1月份的情况
BEGIN
SET @PreYear = @Ayear -1
SET @BsettlementTime = CONVERT(VARCHAR(4),@PreYear)+'-12'+'-'+CONVERT(VARCHAR(4),@BEGINday)
SET @EsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-01'+'-'+CONVERT(VARCHAR(4),@ENDday)
END
ELSE
BEGIN
SET @BsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-'+CONVERT(VARCHAR(4),@PreMonth)+'-'+CONVERT(VARCHAR(4),@BEGINday)
SET @EsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-'+CONVERT(VARCHAR(4),@Amonth)+'-'+CONVERT(VARCHAR(4),@ENDday)
END
END
IF(@Aday>=@BEGINday AND @Aday>=@BEGINday ) --本月和下月（大于）
BEGIN
SET @NextMonth =@Amonth+1
IF(@NextMonth >=13)----12月份的情况
BEGIN
SET @NextYear = @Ayear +1
SET @BsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-12'+'-'+CONVERT(VARCHAR(4),@BEGINday)
SET @EsettlementTime = CONVERT(VARCHAR(4),@NextYear)+'-01'+'-'+CONVERT(VARCHAR(4),@ENDday)
END
ELSE
BEGIN
SET @BsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-'+CONVERT(VARCHAR(4),@Amonth)+'-'+CONVERT(VARCHAR(4),@BEGINday)
SET @EsettlementTime = CONVERT(VARCHAR(4),@Ayear)+'-'+CONVERT(VARCHAR(4),@NextMonth)+'-'+CONVERT(VARCHAR(4),@ENDday)
END
END
IF(@BsettlementTime <=GETDATE() AND @EsettlementTime >= GETDATE())
BEGIN
SET @returnval = 0
END
ELSE
BEGIN
SET @returnval = 1
END
INSERT @table SELECT @BsettlementTime,@EsettlementTime,@returnval
RETURN
END
GO
|
