sql server 标值量函数

当需要处理一些数据，返回想要的数据的 时候 使用标值量函数

结构

CREATE FUNCTION 函数名称
(
@i int=0   函数需要的参数
)
RETURNS int 返回数据的类型
AS
BEGIN
Declare @ResultVar int=0  --声明返回的变量
--业务代码 此处省略
RETURN @ResultVar  返回变量


END
GO

案例

CREATE FUNCTION [dbo].[GetSetdate] 
(
@Settlementrule NVARCHAR(500)='',
@Settime  DATETIME
)
RETURNS DATETIME
AS
BEGIN
--只取到月份
DECLARE @MM VARCHAR(8)=''
DECLARE @Setdate DATETIME
--固定日期
IF(CHARINDEX('Fixed',@Settlementrule)>0)
BEGIN
   DECLARE @daynuber INT=0
   DECLARE @datestarone DATETIME
   DECLARE @datestartwo DATETIME
   DECLARE @daymax INT =0
   
--每月的固定日期
IF(CHARINDEX('MM',@Settlementrule)>0)
BEGIN
SET @daynuber =CAST(REPLACE(@Settlementrule,'Fixed:MM:','') AS INT)-1
SET @MM =CONVERT(varchar(100), @Settime, 23)
SET @datestarone= DATEADD(MM,1,@MM+'01')
SET @datestartwo= DATEADD(MM,2,@MM+'01')
SET @daymax= DATEDIFF(DD,@datestarone,@datestartwo)

IF(@daymax>=@daynuber+1)
SET @Setdate=DATEADD(DD,@daynuber,@datestarone ) 
ELSE
SET @Setdate=DATEADD(DD,@daymax-1,@datestarone ) 

END
--每周的固定周几
ELSE IF(CHARINDEX('WE',@Settlementrule)>0)
BEGIN
SET @daynuber =CAST(REPLACE(@Settlementrule,'Fixed:WE:','') AS INT)
SET @Setdate= DATEADD(DD,7-(DATEPART(WEEKDAY,CONVERT(varchar(100), @Settime, 23))-1)+@daynuber,CONVERT(varchar(100), @Settime, 23)) 
END

END
ELSE IF(CHARINDEX('Cycle',@Settlementrule)>0)
BEGIN
--每月周期
IF(CHARINDEX('MM',@Settlementrule)>0)
BEGIN
SET @daynuber =CAST(REPLACE(@Settlementrule,'Cycle:MM:','') AS INT)

SET @Setdate=DATEADD(MM,@daynuber,CONVERT(varchar(100), @Settime, 23) ) 

END
--每周的周期
ELSE IF(CHARINDEX('WE',@Settlementrule)>0)
BEGIN
SET @daynuber =CAST(REPLACE(@Settlementrule,'Cycle:WE:','') AS INT)
SET @Setdate=DATEADD(week,@daynuber,CONVERT(varchar(100), @Settime, 23) ) 
END
ELSE IF(CHARINDEX('DD',@Settlementrule)>0)
BEGIN
SET @daynuber =CAST(REPLACE(@Settlementrule,'Cycle:DD:','') AS INT)
SET @Setdate=DATEADD(DD,@daynuber,CONVERT(varchar(100), @Settime, 23) ) 
END
END

RETURN @Setdate


END