存储过程--自己工作中涉及到的
-- ================================================
修改contentsubject中对应表关系
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE SP_UpdateContentSubject
AS
/*存储过程开始*/
if object_id('#temp','u') is not null
DROP table #temp
else
Create Table #temp(newsubjectid int,newcontentid int,tempkindid int, tempinfoid int)
if object_id('#temp','u') is not null
DROP table #temptop
else
Create Table #temptop(newsubjectid int,newcontentid int,tempkindid int, tempinfoid int)
Insert into #temp(newsubjectid,newcontentid,tempkindid,tempinfoid) SELECT s.id AS newsubjectid, c.contentID AS newcontentid, c.tempkindid, c.tempinfoid
FROM WM_Kind AS k LEFT OUTER JOIN
Subject AS s ON s.NAME = k.KindName INNER JOIN
[Content] AS c ON c.tempkindid = k.KindID + 100000
where s.id is not null
/*select * from #temp*/
Declare @newsubjectid int
/*select newsubjectid from #temptop*/
Declare @newcontentid int
Declare @tempkindid int
Declare @tempinfoid int
While exists(select top (1) newsubjectid from #temp order by newsubjectid desc,newcontentid desc)
Begin/*While开始*/
Insert into #temptop(newsubjectid,newcontentid,tempkindid,tempinfoid) select top (1) newsubjectid,newcontentid,tempkindid,tempinfoid from #temp order by newsubjectid desc,newcontentid desc
/*select * from #temptop*/
set @newsubjectid=null
set @newcontentid=null
set @tempkindid=null
set @tempinfoid=null
select @newsubjectid = newsubjectid from #temptop
select @newcontentid = newcontentid from #temptop
select @tempinfoid = tempinfoid from #temptop
select @tempkindid = tempkindid from #temptop
print @newsubjectid
print @newcontentid
print @tempinfoid
print @tempkindid
update contentsubject set ContentID = @newcontentid,SubjectID=@newsubjectid where tempinfoid=@tempinfoid and tempkindid=@tempkindid
if exists(select * from contentsubject where ContentID= @newcontentid and SubjectID=@newsubjectid)
begin
Delete #temp where tempinfoid= @tempinfoid and tempkindid=@tempkindid
Delete #temptop where tempinfoid= @tempinfoid and tempkindid=@tempkindid
end
End/*While结束*/
/*存储过程结束*/
GO
Exec SP_UpdateContentSubject
-- ================================================
替换text格式文本中的字符
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE SP_UpdateImgURL
AS
/*存储过程开始*/
if object_id('#temp','u') is not null
DROP table #temp
else
Create Table #temp(contentid int,contentInfo text)
if object_id('#temptop','u') is not null
DROP table #temptop
else
Create Table #temptop(contentid int,contentInfo text)
Insert into #temp(contentid,contentInfo)
select contentID,[Content]
from [CONTENT]
WHERE ([CONTENT] LIKE '%/xhajwebadmin/ewebeditor/UploadFile/%')
/*select * from #temp*/
Declare @contentID int
Declare @contentInfo varchar(max)
While exists(select top (1) contentID from #temp order by contentID desc)
Begin/*While开始*/
Insert into #temptop(contentid,contentInfo) select top (1) contentid,contentInfo from #temp order by contentid desc
/*select * from #temptop*/
set @contentid=null
set @contentInfo=null
select @contentid = contentid from #temptop
select @contentInfo = contentInfo from #temptop
print @contentInfo
set @contentInfo = REPLACE(@contentInfo, '/xhajwebadmin/ewebeditor/UploadFile/', 'http://www.cnblogs.com/http://www.cnblogs.com/UpLoadPath/editor/')
print @contentInfo
update [content] set [CONTENT] = @contentInfo where contentid=@contentid
if exists(select * from [content] where ContentID= @ContentID)
begin
Delete #temp where ContentID= @ContentID
Delete #temptop where ContentID= @ContentID
end
End/*While结束*/
/*存储过程结束*/
GO
Exec SP_UpdateImgURL