cs_Folder_GetFeeds
ALTER PROC [dbo].cs_Folder_GetFeeds
@UserId INT,
@SettingsID int,
@FolderId INT = NULL
AS
/**//*如果为空执行如下查询,不为空,再执行如下查询。好象什么都没说的样子,省略…………*/
IF (@FolderId IS NULL)
BEGIN
SELECT cf1.FeedId,
cf1.Url,
cf1.Title,
cf1.Link,
cf1.ETag,
cf1.LastModified,
cf1.Language,
cf1.Generator,
cf1.SubscribeDate,
cf1.LastUpdateDate,
cf1.FeedStateId,
cff.FolderFeedId
,[UnreadCount] = ( select count(*) from (Select top 50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf1.FeedId order by PubDate DESC) fp left outer join cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cff.FeedId and urp.UserId IS NULL )
FROM cs_Feed AS cf1,
cs_FolderFeed AS cff
WHERE cff.FeedId = cf1.FeedId
AND cff.UserId = @UserId
AND cff.FolderId IS NULL
AND cf1.SettingsID = @SettingsID
ORDER BY cf1.Title
END
ELSE
BEGIN
SELECT cf1.FeedId,
cf1.Url,
cf1.Title,
cf1.Link,
cf1.Language,
cf1.Generator,
cf1.ETag,
cf1.LastModified,
cf1.SubscribeDate,
cf1.LastUpdateDate,
cf1.FeedStateId,
cff.FolderFeedId
,[UnreadCount] = ( select count(*) from (Select top 50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf1.FeedId order by PubDate DESC) fp left outer join cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cff.FeedId and urp.UserId IS NULL )
FROM cs_Feed AS cf1,
cs_FolderFeed AS cff
WHERE cff.FeedId = cf1.FeedId
AND cff.UserId = @UserId
AND cff.FolderId = @FolderId
AND cf1.SettingsID = @SettingsID
ORDER BY cf1.Title
END
ALTER PROC [dbo].cs_Folder_GetFeeds
@UserId INT,
@SettingsID int,
@FolderId INT = NULL
AS
/**//*如果为空执行如下查询,不为空,再执行如下查询。好象什么都没说的样子,省略…………*/
IF (@FolderId IS NULL)
BEGIN
SELECT cf1.FeedId,
cf1.Url,
cf1.Title,
cf1.Link,
cf1.ETag,
cf1.LastModified,
cf1.Language,
cf1.Generator,
cf1.SubscribeDate,
cf1.LastUpdateDate,
cf1.FeedStateId,
cff.FolderFeedId
,[UnreadCount] = ( select count(*) from (Select top 50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf1.FeedId order by PubDate DESC) fp left outer join cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cff.FeedId and urp.UserId IS NULL )
FROM cs_Feed AS cf1,
cs_FolderFeed AS cff
WHERE cff.FeedId = cf1.FeedId
AND cff.UserId = @UserId
AND cff.FolderId IS NULL
AND cf1.SettingsID = @SettingsID
ORDER BY cf1.Title
END
ELSE
BEGIN
SELECT cf1.FeedId,
cf1.Url,
cf1.Title,
cf1.Link,
cf1.Language,
cf1.Generator,
cf1.ETag,
cf1.LastModified,
cf1.SubscribeDate,
cf1.LastUpdateDate,
cf1.FeedStateId,
cff.FolderFeedId
,[UnreadCount] = ( select count(*) from (Select top 50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf1.FeedId order by PubDate DESC) fp left outer join cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cff.FeedId and urp.UserId IS NULL )
FROM cs_Feed AS cf1,
cs_FolderFeed AS cff
WHERE cff.FeedId = cf1.FeedId
AND cff.UserId = @UserId
AND cff.FolderId = @FolderId
AND cf1.SettingsID = @SettingsID
ORDER BY cf1.Title
END
cs_Folder_DeleteFolder
ALTER PROC [dbo].cs_Folder_DeleteFolder
@FolderId INT
AS
-- First, let's delete any associated feeds.
--先删除关联表中的记录
DELETE
FROM cs_FolderFeed
WHERE FolderId = @FolderId
-- Now, delete the folder.
--然后再删除主表的记录
DELETE
FROM cs_Folder
WHERE FolderId = @FolderId
ALTER PROC [dbo].cs_Folder_DeleteFolder
@FolderId INT
AS
-- First, let's delete any associated feeds.
--先删除关联表中的记录
DELETE
FROM cs_FolderFeed
WHERE FolderId = @FolderId
-- Now, delete the folder.
--然后再删除主表的记录
DELETE
FROM cs_Folder
WHERE FolderId = @FolderId
cs_Folder_AddFolder
ALTER PROC [dbo].cs_Folder_AddFolder
@UserID INT,
@FolderName NVARCHAR(50),
@ParentFolderId INT = NULL,
@SettingsID INT,
@FolderId INT OUTPUT
AS
/**//*添加记录,然后返回ID*/
INSERT INTO cs_Folder
(
UserId,
FolderName,
ParentFolderId,
SettingsID
)
VALUES
(
@UserId,
@FolderName,
@ParentFolderId,
@SettingsID
)
SELECT @FolderId = @@IDENTITY
ALTER PROC [dbo].cs_Folder_AddFolder
@UserID INT,
@FolderName NVARCHAR(50),
@ParentFolderId INT = NULL,
@SettingsID INT,
@FolderId INT OUTPUT
AS
/**//*添加记录,然后返回ID*/
INSERT INTO cs_Folder
(
UserId,
FolderName,
ParentFolderId,
SettingsID
)
VALUES
(
@UserId,
@FolderName,
@ParentFolderId,
@SettingsID
)
SELECT @FolderId = @@IDENTITY