cs_FeedPost_UpdatePosts
ALTER PROC [dbo].cs_FeedPost_UpdatePosts
@FeedId INT,
@FeedItemList NTEXT
AS
SET NOCOUNT ON
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
)
/**//*声明临时表*/
EXEC sp_xml_preparedocument @idoc OUTPUT, @FeedItemList
/**//*不知道是不是处理那个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
-- 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
)
-- 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
EXEC sp_xml_removedocument @idoc
ALTER PROC [dbo].cs_FeedPost_UpdatePosts
@FeedId INT,
@FeedItemList NTEXT
AS
SET NOCOUNT ON
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
)
/**//*声明临时表*/
EXEC sp_xml_preparedocument @idoc OUTPUT, @FeedItemList
/**//*不知道是不是处理那个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
-- 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
)
-- 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
EXEC sp_xml_removedocument @idoc
cs_FeedPost_GetPostFullDetails
ALTER PROC [dbo].cs_FeedPost_GetPostFullDetails
@FeedId INT
AS
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
/**//*再省略………………*/
ALTER PROC [dbo].cs_FeedPost_GetPostFullDetails
@FeedId INT
AS
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
/**//*再省略………………*/
cs_FeedPost_GetPost
ALTER PROC [dbo].cs_FeedPost_GetPost
@FeedPostId INT
AS
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
/**//*省略………………*/
ALTER PROC [dbo].cs_FeedPost_GetPost
@FeedPostId INT
AS
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
/**//*省略………………*/
cs_Feed_UpdateFeedStatus
ALTER PROC [dbo].cs_Feed_UpdateFeedStatus
@FeedId INT,
@FeedStateId INT
AS
UPDATE cs_Feed
SET FeedStateId = @FeedStateId,
LastUpdateDate = GetDate()
WHERE FeedId = @FeedId
/**//*更新最近更新时间 */
ALTER PROC [dbo].cs_Feed_UpdateFeedStatus
@FeedId INT,
@FeedStateId INT
AS
UPDATE cs_Feed
SET FeedStateId = @FeedStateId,
LastUpdateDate = GetDate()
WHERE FeedId = @FeedId
/**//*更新最近更新时间 */