--===================================================================
--Author:wuyang
--CreateDate:2017-1-13
--Desc:CMS转移
--eg:把人南路社区(011002002002048004)的信息数据迁移至青石桥社区(011002002002048002),然后删除人南路社区。
--===================================================================
CREATE PROCEDURE [dbo].[CMSGroupChange]
(
@oldid NVARCHAR(100),--转移id
@newid NVARCHAR(100),--新id
@result INT OUT --1:成功 0:失败
)
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @oldgroupid NVARCHAR(100);
DECLARE @newgroupid NVARCHAR(100);
DECLARE @oldusername NVARCHAR(100);
DECLARE @newusername NVARCHAR(100);
IF EXISTS(SELECT * FROM gpsp_sitemapping WHERE old_id=@oldid) and EXISTS(SELECT * FROM gpsp_sitemapping WHERE old_id=@newid)
BEGIN
--1.根据id查询站点名称和站点id
SELECT @oldgroupid=new_id,@oldusername=old_name FROM gpsp_sitemapping WHERE old_id=@oldid;
SELECT @newgroupid=new_id,@newusername=old_name FROM gpsp_sitemapping WHERE old_id=@newid;
print @oldusername+@oldgroupid+' 中的数据即将转移到 '+@newusername+@newgroupid+'中';
--2.开始转移 update CMSPublishedArticle(更新站点id和plid)
update CMSPublishedArticle set groupid = @newgroupid,plid =
(select l.plid ) from CMSPublishedArticle c,JournalArticle j ,Layout l
where
c.groupId = @oldgroupid and
c.resourcePrimKey = j.resourcePrimKey and
l.uuid_ = j.layoutUuid and
l.groupId = @newgroupid;
--3.update JournalArticle(更新站点id)
update JournalArticle set groupId = @newgroupid where groupId = @oldgroupid;
--4.update AssetEntry(更新站点di)
update AssetEntry set groupId = @newgroupid where groupId = @oldgroupid and classNameId = '10109';
--5.delete gpsp_site(删除旧站点)
delete from gpsp_site where siteid = @oldgroupid;
--6.delete gpsp_sitemapping(删除旧站点映射关系)
delete from gpsp_sitemapping where new_id = @oldgroupid
--7.跟新CMS显示来源名称
update CMSPublishedArticle set userName = @newusername where groupId = @newgroupid
END
ELSE
BEGIN
SET @result=0;
print '错误,不存在站点';
END
IF @@ERROR<>0
BEGIN
SET @result=0;
ROLLBACK TRANSACTION
RETURN ;
END
ELSE
BEGIN
SET @result=1;
COMMIT TRANSACTION
END
END
GO
Sql Server 2012 存储过程Demo1
最新推荐文章于 2022-10-12 14:49:09 发布