ALTER procedure P_Test(@startdate nvarchar(50),@enddate nvarchar(50),@companyid int,@depid nvarchar(50))
as
declare @id int --定义变量来保存ID号
declare @code_cursor nvarchar(50) --定义变量来保存ID号
begin tran
declare mycursor cursor for
select
A.code
from dbo.T_OfficeCardException A left outer join T_EMPLOYEEBASEINFO D on D.id=A.creater
where A.code>=@startdate and A.code<=@enddate and A.companyid=@companyid
and A.departmentid in (select short_str FROm [dbo].[split](@depid, ',')
)--为所获得的数据集指定游标
open mycursor --打开游标
--region 创建临时表
if object_id('tempdb..#tems') is not null
begin
drop table tempdb..#tems
-- truncate table #tems --清空临时表的所有数据和约束
end
else
begin
create table #tems
(id int,
companyid int,
code nvarchar(50)
)
end
fetch next from mycursor into @code_cursor--开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
insert into #tems(id,code)
select
TOP 1
A.id,
@code_cursor from T_Back_EMPLOYEEBASEINFO A
if(@@error<>0)
begin
rollback tran
end
else
begin
fetch next from mycursor into @code_cursor--开始抓第一条数据
end
end
select * from #tems
close mycursor --关闭游标
deallocate mycursor --删除游标
commit tran
