sqlserver-with

声明两个with是以，分割就可以了

ALTER PROCEDURE [dbo].[sp_getDrOfmoduleCodeTrace]
(
@vob nvarchar(255),
@version nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
--  +-split-+代表的是换行符
set @vob = replace(@vob,'+-split-+',char(10));
--程序传过来的vob是分隔符是/,而数据库中vob分隔符是\
set @vob = replace(@vob,'/','\');
print @vob;
DECLARE @RunSQL nvarchar(MAX);
--统计表
declare @mctTable varchar(255);
--link表
declare @linkTable varchar(255);
declare @StoryTable varchar(255);
declare @RMTable varchar(255);

set @mctTable = 'E3_MODULE_STATISTIC_' + @version;
set @linkTable = 'E3_LINK_' + @version;
set @StoryTable = 'IKNOW_STORY_' + @version;
set @RMTable = 'IKNOW_RMPUBLISHTREE_' + @version;

--如果所需要的表不存在，则存储过程结束
IF not EXISTS (SELECT * FROM sysobjects WHERE name = @mctTable)
return;
else if not EXISTS (SELECT * FROM sysobjects WHERE name = @linkTable)
return;
else if not EXISTS (SELECT * FROM sysobjects WHERE name = @StoryTable)
return;

set @RunSQL = '
with vob
as
(
select distinct realvob from E3_MODULE_STATISTIC_U2000V1R8C00
where vob = ''' + @vob + '''
),
dr_code
as
(
select distinct drcode from ' + @StoryTable + ' a
inner join ' + @RMTable + ' b
on a.drcode = b.rmid
where jirakey in
(
select distinct destid from ' + @linkTable + ' a
where DESTTYPE=''STORY'' and
exists(
select * from vob b
where a.SOURCEID like b.realvob + ''%''
)
) and drcode !='''''
+ ')
select ITEMGUID as id,name,tdtname,creator,CREATIONDATE as createddate,''DR'' as type,''' + @version + ''' as version from ' + @RMTable + '
where rmid in(select * from dr_code)';

EXECUTE SP_EXECUTESQL @RunSQL;
END