菜单
一、表值函数
1.1 不带参数的表值函数
CREATE FUNCTION [dbo].[Azure_EST_GetDate] (
)
RETURNS TABLE
AS
RETURN
(
SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2(3)) AS handate
);
GO1.2 带参数的表值函数
递归查看部门
ALTER FUNCTION [dbo].[DepartLongName](@kID varchar(50))
returns table
as
return
(
WITH DirectReports(id,name,supdepid,longname)
AS
(
select d1.id,departmentmark name,supdepid,CONVERT (varchar(1000),c.subcompanydesc+'\'+departmentmark) longname
from hrmdepartment d1
left join hrmsubcompany c on d1.subcompanyid1=c.id
where 1=1 and supdepid=0
UNION ALL
select d2.id,d2.departmentmark,d2.supdepid,CONVERT (varchar(1000),d.longname+'\'+d2.departmentmark)
from hrmdepartment d2
inner join DirectReports AS d on d2.supdepid=d.id
where 1=1
)
select * from DirectReports where 1=1
)1.3 带有判断的表值函数
ALTER FUNCTION [dbo].[OA_JFInfo](@month datetime,@type varchar(50),@userid int )
RETURNS @tb TABLE
(
lastname varchar(250) NOT NULL,
loginid VARCHAR(200) NOT NULL,
DepartName varchar(250) NOT NULL,
jfye decimal(8,2) NOT NULL,
Totaljf decimal(8,2) NOT NULL,
FDate varchar(20),
id int,
remark varchar(500)
)
AS
BEGIN
IF @type='LastSeason'
begin
INSERT INTO @tb
select ********
end
IF @type='LastYear'
begin
INSERT INTO @tb
select ********
end
IF @type='ThisYear'
begin
INSERT INTO @tb
select ********
end
RETURN
END
-- select * from OA_JFInfo(getdate(),'JFDetail',4347)IF判断是否存在记录
if not exists(select * from sysobjects where name='temp_log')
begin
####do something
end
else
begin
####do something
end
二、标量函数
ALTER FUNCTION [dbo].[OA_GetFlowView] (@requestID varchar(250)) RETURNS varchar(max)
AS
BEGIN
/* Function body */
declare @return varchar(max)
declare @TIMESTAMP integer
set @TIMESTAMP=DATEDIFF(SECOND,{d '1970-01-01'}, getdate())
select @return='http://oa1.ganioa.com/spa/workflow/static4mobileform/index.html?_random='+
cast( @TIMESTAMP as varchar(150)) +'#/req?requestid='+cast(@requestID as varchar(50))
return @return
END
三、存储过程
3.1 返回表值存储过程
存储过程中，可以执行insert select update delete
ALTER PROCEDURE [dbo].[OA_GetImportProject]
-- Add the parameters for the stored procedure here
@UserAmount varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/* 可定义参数 也可以定义临时表*/
-- Insert statements for procedure here
declare @loginid varchar(max)
select @loginid=id from Hrmresource where loginid=@UserAmount
select ****
END
--exec OA_GetImportProject 'FS201702002'四、循环和游标
4.1 数据库创建一个task_test表
4.2 循环例子
本例子中，循环100次，能被5整除插入taskid
declare @count int
declare @i int
set @count=100;
set @i=0;
while @i<@count --返回被 FETCH语句执行的最后游标的状态--
begin
if @i%5=0
begin
insert into task_test(guidValue,taskid,taskName)
values(newid(),@i,'数据')
end
else
begin
insert into task_test(taskid,taskName)
values(@i,'数据')
end
set @i+=1;
end
select @i
select * from task_test通过游标处理，没有GUID的数据
declare @a int,@error int
declare @temp varchar(50)
set @a=1
set @error=0
--申明游标为id
declare table_cursor cursor
for (select FID from task_test where guidValue is null)
--打开游标--
open table_cursor
--开始循环游标变量--
fetch next from table_cursor into @temp --将数据插入临时表
select 'FID'+cast(@temp as varchar(20))
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
update task_test set guidValue=newid() where FID=@temp
set @a=@a+1
set @error= @error + @@ERROR --记录每次运行sql后是否正确，0正确
fetch next from table_cursor into @temp --转到下一个游标，没有会死循环
end
close table_cursor --关闭游标
deallocate table_cursor --释放游标
--delete from task_test存储过程中动态sql并赋值变量
create PROCEDURE [dbo].[sp_oa_CreateContractNumber] @TableName nvarchar(250), @RequestId nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Sql nvarchar(3000),@gsbm nvarchar(250),@bmsx nvarchar(250),@zllbbm nvarchar(250),
@zlnrflsx nvarchar(250),@yearmon nvarchar(250),@MAXNum int
declare @C_Id int
declare @Cou int
set @Sql=' select @CId=t.id,@Cou=t.requestId,@s_gsbm=c.gsbm,@s_bmsx=b.bmsx,@s_zllbbm=lb.zllbbm,
@s_zlnrflsx=fl.zlnrflsx,@s_yearmon=convert(varchar(6),cast(u.modedatacreatedate as date),112),
@s_MAXNum = (select cast(count(*)+1 as varchar(50)) from uf_htgl where htzt in(1,2) and
htgzgs=t.dagzgs2 and htgzbm=t.htgzbm and DATEDIFF(yy,modedatacreatedate,getdate()) =0
)
from uf_htgl u
left join '+@TableName+' t on t.requestId=u.htpsid
left join uf_gsmc c on c.id=t.dagzgs2
left join uf_bm b on b.id=t.htgzbm
left join uf_zllb lb on lb.id=t.htzllb
left join uf_zlnrfl fl on fl.id=t.htzlfl
where 1=1 and t.requestid = '''+@RequestId+''''
exec sp_executesql @Sql,N'@CId int output,@cou int output,
@s_gsbm nvarchar(250) output,@s_bmsx nvarchar(250) output,@s_zllbbm nvarchar(250) output,
@s_zlnrflsx nvarchar(250) output,@s_yearmon nvarchar(250) output,@s_MAXNum int output',
@CId = @C_Id output,
@cou = @Cou output,
@s_gsbm = @gsbm output,
@s_bmsx = @bmsx output,
@s_zllbbm = @zllbbm output,
@s_zlnrflsx = @zlnrflsx output,
@s_yearmon = @yearmon output,
@s_MAXNum = @MAXNum output;
--select @C_Id,@Cou,@gsbm,@bmsx,@zllbbm,@zlnrflsx,@yearmon,@MAXNum
declare @maxcount int
declare @SerialNumber int
declare @Finish_htbh varchar(250)
set @maxcount=@MAXNum+200
set @SerialNumber=@MAXNum
while @SerialNumber<@maxcount --返回被 FETCH语句执行的最后游标的状态--
begin
set @Finish_htbh = @gsbm+'-'+@bmsx+'-'+@zllbbm+'-'+@zlnrflsx+'-'+cast(@SerialNumber as varchar(100))+'-'+@yearmon
if not exists ( select 1 from uf_htgl where htbh=@Finish_htbh)
begin
--select @Finish_htbh;
--select * from
update h set h.htbh=@Finish_htbh from
uf_htgl h where 1=1 and isnull(h.htbh,'')='' and h.htpsid=@RequestId
BREAK;
end
set @SerialNumber+=1;
end
END