![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_FeedPost_UpdatePosts
ALTER PROC [dbo].cs_FeedPost_UpdatePosts
@FeedId INT,
@FeedItemList NTEXT
AS
![](/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
![](/Images/OutliningIndicators/None.gif)
DECLARE @idoc INT
DECLARE @FeedPosts TABLE
(
FeedId INT,
Author NVARCHAR(255),
Title NVARCHAR(255),
Description NTEXT,
Source NVARCHAR(255),
GuidName NVARCHAR(255),
GuidIsPermaLink BIT,
Link NVARCHAR(255),
PubDate DATETIME,
CommentsUrl NVARCHAR(255),
EnclosureUrl VARCHAR(255),
EnclosureLength BIGINT,
EnclosureType NVARCHAR(100),
Creator NVARCHAR(255) NULL,
CommentApiUrl NVARCHAR(255) NULL,
CommentRssUrl NVARCHAR(255) NULL,
CommentCount INT NULL
)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*声明临时表*/
EXEC sp_xml_preparedocument @idoc OUTPUT, @FeedItemList
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*不知道是不是处理那个RSS文件什么的东西,看这好象有点像。再省略………………
*/
-- First off, let's move all the XML into the table variable.
INSERT INTO @FeedPosts
SELECT C.FeedId,
C.Author,
C.Title,
C.Description,
C.Source,
C.GuidName,
C.GuidIsPermaLink,
C.Link,
C.PubDate,
C.CommentsUrl,
C.EnclosureUrl,
C.EnclosureLength,
C.EnclosureType,
C.Creator,
C.CommentApiUrl,
C.CommentRssUrl,
C.CommentCount
FROM OPENXML(@idoc, '/feeds/feed', 3)
WITH ( FeedId INT,
Author NVARCHAR(255),
Title NVARCHAR(255),
Description NTEXT,
Source NVARCHAR(255),
GuidName NVARCHAR(255),
GuidIsPermaLink BIT,
Link NVARCHAR(255),
PubDate DATETIME,
CommentsUrl NVARCHAR(255),
EnclosureUrl VARCHAR(255),
EnclosureLength BIGINT,
EnclosureType NVARCHAR(100),
Creator NVARCHAR(255),
CommentApiUrl NVARCHAR(255),
CommentRssUrl NVARCHAR(255),
CommentCount INT) AS C
![](/Images/OutliningIndicators/None.gif)
-- Insert missing posts
INSERT INTO cs_FeedPost
(
FeedId,
Author,
Title,
Description,
Source,
GuidName,
GuidIsPermaLink,
Link,
PubDate,
CommentsUrl,
EnclosureUrl,
EnclosureLength,
EnclosureType,
Creator,
CommentApiUrl,
CommentRssUrl,
CommentCount
)
SELECT C.FeedId,
C.Author,
C.Title,
C.Description,
C.Source,
C.GuidName,
C.GuidIsPermaLink,
C.Link,
C.PubDate,
C.CommentsUrl,
C.EnclosureUrl,
C.EnclosureLength,
C.EnclosureType,
C.Creator,
C.CommentApiUrl,
C.CommentRssUrl,
C.CommentCount
FROM @FeedPosts AS C
WHERE C.GuidName NOT IN (
SELECT GuidName FROM cs_FeedPost
WHERE FeedId = @FeedId
)
![](/Images/OutliningIndicators/None.gif)
-- Update existing posts.
UPDATE cs_FeedPost
SET Author = C.Author,
Title = C.Title,
Description = C.Description,
Source = C.Source,
GuidName = C.GuidName,
GuidIsPermaLink = C.GuidIsPermaLink,
Link = C.Link,
PubDate = C.PubDate,
CommentsUrl = C.CommentsUrl,
EnclosureUrl = C.EnclosureUrl,
EnclosureLength = C.EnclosureLength,
EnclosureType = C.EnclosureType,
Creator = C.Creator,
CommentApiUrl = C.CommentApiUrl,
CommentRssUrl = C.CommentRssUrl,
CommentCount = C.CommentCount
FROM @FeedPosts AS C
WHERE cs_FeedPost.GuidName = C.GuidName
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
EXEC sp_xml_removedocument @idoc
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_FeedPost_GetPostFullDetails
ALTER PROC [dbo].cs_FeedPost_GetPostFullDetails
@FeedId INT
AS
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT fp.FeedPostId,
fp.FeedId,
fp.Author,
fp.Title,
fp.Description,
fp.Source,
fp.GuidName,
fp.GuidIsPermaLink,
fp.Link,
fp.PubDate,
fp.CommentsUrl,
fp.EnclosureUrl,
fp.EnclosureLength,
fp.EnclosureType,
fp.Creator,
fp.CommentApiUrl,
fp.CommentRssUrl,
fp.CommentCount
FROM cs_FeedPost fp,
cs_Feed f
WHERE f.FeedId = fp.FeedId
AND fp.FeedPostId = @FeedId
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*再省略………………*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_FeedPost_GetPost
ALTER PROC [dbo].cs_FeedPost_GetPost
@FeedPostId INT
AS
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT FeedPostId,
FeedId,
Author,
Title,
Description,
Source,
GuidName,
GuidIsPermaLink,
Link,
PubDate,
CommentsUrl,
EnclosureUrl,
EnclosureLength,
EnclosureType,
Creator,
CommentApiUrl,
CommentRssUrl,
CommentCount
FROM cs_FeedPost
WHERE FeedPostId = @FeedPostId
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*省略………………*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_Feed_UpdateFeedStatus
ALTER PROC [dbo].cs_Feed_UpdateFeedStatus
@FeedId INT,
@FeedStateId INT
AS
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
UPDATE cs_Feed
SET FeedStateId = @FeedStateId,
LastUpdateDate = GetDate()
WHERE FeedId = @FeedId
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*更新最近更新时间 */
![](/Images/OutliningIndicators/None.gif)
posted on
2006-09-20 11:37
MainIsUsed
阅读(
238)
评论()
编辑
收藏
举报