存储过程--自己工作中涉及到的

-- ================================================
修改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