cs_folder_getfolders.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_Folder_GetFolders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Folder_GetFolders]
GO


CREATE PROC [dbo].cs_Folder_GetFolders
	@UserId INT,
	@SettingsID INT,
	@ParentFolderId INT = -1
AS
Begin


	Create Table #Temp ( FeedId int, FolderId int, CNT int )
	
	Insert #Temp (FeedId, FolderId, CNT)
	Select FeedId, cf.FolderId,
	(
		select count(*) from (Select top 50 f.FeedPostID, f.FeedId FROM cs_FeedPost f where f.FeedId = cf.FeedId order by PubDate DESC) fp left outer join cs_UserReadPost urp on fp.FeedPostID = urp.FeedPostID where fp.FeedId = cf.FeedId and urp.UserId IS NULL
	) as [CNT] FROM cs_FolderFeed cf, cs_Folder ff where cf.FolderId = ff.FolderId and  IsNull(ff.ParentFolderId, -1) = @ParentFolderId and cf.UserId = @UserId and cf.SettingsID = @SettingsID and ff.SettingsID = @SettingsID


	SELECT 	FolderId,
		UserId,
		FolderName,
		ParentFolderId
		,[UnreadCount] = (Select Sum(CNT) FROM #Temp where #Temp.FolderId = cs_Folder.FolderID)
	FROM cs_Folder
	WHERE UserId = @UserId
	  AND IsNull(ParentFolderId,-1) =  @ParentFolderId
          AND cs_Folder.SettingsID = @SettingsID
	ORDER BY FolderName



	DROP Table #Temp

  END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].cs_Folder_GetFolders  TO PUBLIC
GO