声明两个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
分享到:
相关推荐
SQL Server字符串处理函数大全
Microsoft SQL Server Management Studio Express(SSMSE)是一种免费、易于使用的图形管理工具,用于管理SQL Server 2005 Express Edition和SQL Server 2005 ExpressEdition with Advanced Services。注意:SSMSE ...
SQLserver CTE With 使用with产出临时表,在根据临时表二次查询
Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through 10-minute lessons, you’ll learn everything you ...
Flink1.14.4自定义flink-connector-jdbc连接SQLServer和SAP数据库
R is one of the most popular, powerful data analytics languages and environments in use ...concepts are explained in case you are not familiar with SQL Server, as is often the case with data scientists.
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming....
Expert SQL Server In-Memory OLTP is a deep dive into one of the most significant features of SQL Server 2014 – support for In-Memory Online Transaction Processing. The book describes the architecture...
The SQL Server client tools must be installed on your desktop computer and the SQL Server relational database server must be installed on either your desktop computer or on a remote server with ...
SQL Server with C# Klaus Elk (Author) Paperback: 146 pages 出版社: CreateSpace Independent Publishing Platform (July 5, 2018) Language: English ISBN-10: 1720358672 ISBN-13: 9781720358671 Contents 1 ...
Builds a strong foundation that gets you comfortable using PowerShell with SQL Server - empowering you to create more complex scripts that you need in your day-to-day job.">Provides over a hundred ...
Wrox.Beginning T-Sql with Microsoft SQL Server 2005 and 2008.pdf
Understand Microsofts dramatically ... Get thoroughly up to speed on this powerful new BI toolset with the timely and authoritative "Professional Microsoft SQL Server 2012 Analysis Services with MDX".
针对sqlserver 2008 存储过程通过With Encryption加密方式的解密
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming. Beginning with an ...
dbExpress driver for SQL Server with 64-bit Support
Beginning Transact-SQL with SQL Server 2000 and 2005 byPaul TurleyandDan Wood Wrox Press 2006 (594 pages) ISBN:076457955X Prepare for the ever-increasing demands of programming. Beginning with an ...
SQL Server only started to fully support data science with its two most recent editions. If you are a professional from both worlds, SQL Server and data science, and interested in using SQL Server and...