cs_feedpost_updateposts.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_FeedPost_UpdatePosts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_FeedPost_UpdatePosts]
GO



CREATE 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) collate database_default,
	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

-- 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



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].cs_FeedPost_UpdatePosts  TO PUBLIC
GO