create procedure SP_Wim_GetWorkSubSectionById(@paramId as int)
as
begin
declare @id as int;
declare @lvId as [nvarchar](20);
declare @mainID as [nvarchar](100);
declare @wsLv as int;
declare @wsState as int;
declare @resolveState as int;
declare @wsName as [nvarchar](100);
declare @wsTarget as [nvarchar](500);
declare @wsManager as [int];
declare @pBeginDt as [datetime];
declare @pEndDt as [datetime];
declare @pCycle as int;
create table #Temp(
[id] [int] IDENTITY(1,1) NOT NULL,
[wsId] [int] NULL,
[lvId] [nvarchar](20) NULL,
[mainID] [nvarchar](100) NULL,
[wsLv] [int] NULL,
[wsState] [int] NULL,
[resolveState] [int] NULL,
[wsName] [nvarchar](100) NULL,
[wsTarget] [nvarchar](500) NULL,
[wsManager] [int] NULL,
[pBeginDt] [datetime] NULL,
[pEndDt] [datetime] NULL,
[pCycle] [int] NULL
)
–select * from Wim_WorkSection where id=@paramId
insert into #Temp ([wsId],[lvId],[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],[pCycle]) select [id],’0′,[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],IsNull([pCycle],0) from Wim_WorkSection where id=@paramId
declare @num as varchar(20);
declare @allws as varchar(500);
declare @tempid as int;
DECLARE items_cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
select dbo.[Fuc_GetwsNumber](id) as num,REPLACE(dbo.[Fuc_Getwsallws](id),’|’,’,’) as allws,id from Wim_WorkSection where wsParentID=1106
OPEN items_cursor
FETCH NEXT from items_cursor INTO @num,@allws,@tempid
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Temp ([wsId],[lvId],[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],[pCycle]) select [id],@num,[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],IsNull([pCycle],0) from Wim_WorkSection where id=@tempid
if(@allws!=”)
begin
SELECT @num=@num+’.0′
insert into #Temp ([wsId],[lvId],[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],[pCycle]) select [id],@num,[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],IsNull([pCycle],0) from Wim_WorkSection
where id IN (select id from Wim_WorkSection where wsParentID=@tempid)
end
FETCH NEXT FROM items_cursor INTO @num,@allws,@tempid
END
CLOSE items_cursor
DEALLOCATE items_cursor
–查询临时表的数据
select * from #Temp
end
exec SP_Wim_GetWorkSubSectionById 1106;
drop SP_GetWorkSubSectionById
SELECT * FROM Wim_WorkSection where wsParentID=1106
insert into #Temp ([wsId],[lvId],[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],[pCycle])
select [id],1,[mainID],[wsLv],[wsState],[resolveState],[wsName],[wsTarget],[wsManager],[pBeginDt],[pEndDt],IsNull([pCycle],0) from Wim_WorkSection
where id IN (select id from Wim_WorkSection where wsParentID=1111)
create proc proc_name
as
sql_text
DECLARE cur_name CURSOR
for
sql_text
OPEN items_cursor
FETCH NEXT from cur_name INTO @name….
WHILE @@FETCH_STATUS = 0
BEGIN
sql_text
END
CLOSE cur_name
DEALLOCATE cur_name
