sp.sql

asp.net 商业2.0论坛对理解论坛的制作很用帮助

-- ===== My Topic2 Procdure =====
-- Description:Page My Topic2
-- Author:kid
-- ==============================
CREATE PROCEDURE MyTopic2
(
@PageNow int=1,
@PageSize int=1,
@TopNum int=0,
@UserId varchar(255)
) 
WITH ENCRYPTION
AS 
set nocount on
if @PageNow>1 
	begin
	declare @InfoId int
	declare @MaxId int
	select @MaxId=((@PageNow-1)*@PageSize-@TopNum)
	declare FastRead cursor scroll for
	select Forum_Info_Times from Forum_Info where Forum_Info_Users=@UserId and 
	Forum_Info_IsTop=0 and Forum_Info_ParentId<>0 order by Forum_Info_Times desc
    	open FastRead
	fetch absolute @MaxId from FastRead into @InfoId 
	set rowcount @PageSize
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock  from Forum_Info where Forum_Info_Users=@UserId
	 and Forum_Info_ParentId<>0 and Forum_Info_IsTop=0 and Forum_Info_Times < @InfoId and not Forum_Info_IsLock=1 
	 and Forum_Info_RootId is not null and IsDel='0' ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
   	
	close FastRead 
    	deallocate FastRead 
	end
else	
	begin
	set rowcount @PageSize 
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock  from Forum_Info where 
	Forum_Info_Users=@UserId and Forum_Info_ParentId<>0 and not Forum_Info_IsLock=1 
	and Forum_Info_RootId is not null and IsDel='0' ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
	end
return

GO


-- ===== My Topic Procdure =====
-- Description:Page My Topic
-- Author:kid
-- =============================
CREATE PROCEDURE MyTopic 
(
@PageNow int=1,
@PageSize int=1,
@TopNum int=0,
@UserId varchar(255)
) 
WITH ENCRYPTION
AS 
set nocount on
if @PageNow>1 
	begin
	declare @InfoId int
	declare @MaxId int
	select @MaxId=((@PageNow-1)*@PageSize-@TopNum)
	declare FastRead cursor scroll for
	select Forum_Info_Times from Forum_Info where Forum_Info_Users=@UserId and 
	Forum_Info_IsTop=0 and Forum_Info_ParentId=0 order by Forum_Info_Times desc
    	open FastRead
	fetch absolute @MaxId from FastRead into @InfoId 
	set rowcount @PageSize
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock  from Forum_Info where Forum_Info_Users=@UserId
	 and Forum_Info_ParentId=0 and Forum_Info_IsTop=0 and Forum_Info_Times < @InfoId and not Forum_Info_IsLock=1 
	 and Forum_Info_RootId is not null and IsDel='0' ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
   	
	close FastRead 
    	deallocate FastRead 
	end
else	
	begin
	set rowcount @PageSize 
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock  from Forum_Info where 
	Forum_Info_Users=@UserId and Forum_Info_ParentId=0 and not Forum_Info_IsLock=1 
	and Forum_Info_RootId is not null and IsDel='0' ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
	end
return

GO

-- ===== My Favorite Procdure =====
-- Description:Page My Favorite
-- Author:kid
-- ================================
CREATE PROCEDURE MyFav
(
@PageNow int=1,
@PageSize int=1,
@UserId varchar(255)
) 
WITH ENCRYPTION
AS 
set nocount on
if @PageNow>1 
	begin
	declare @FavId int
	declare @MaxId int
	select @MaxId=((@PageNow-1)*@PageSize)
	declare FastRead cursor scroll for
	select Forum_BookMark_Id from Forum_BookMarks where Forum_User_Name=@UserId 
	order by Forum_BookMark_Id desc
    	open FastRead
	fetch absolute @MaxId from FastRead into @FavId 
	set rowcount @PageSize
	select Forum_BookMark_Name,Forum_BookMark_Des,Forum_Info_Id,Forum_Board.Forum_Board_Name,Forum_BookMark_Time,Forum_Board.Forum_Board_Id
	from Forum_BookMarks,Forum_Board where Forum_Board.Forum_Board_Id=Forum_BookMarks.Forum_Board_Id and  Forum_User_Name=@UserId
	and Forum_BookMark_Id < @FavId ORDER BY Forum_BookMark_Id desc
   	
	close FastRead 
    	deallocate FastRead 
	end
else	
	begin
	set rowcount @PageSize 
	select Forum_BookMark_Name,Forum_BookMark_Des,Forum_Info_Id,Forum_Board.Forum_Board_Name,Forum_BookMark_Time,Forum_Board.Forum_Board_Id
	from Forum_BookMarks,Forum_Board where Forum_Board.Forum_Board_Id=Forum_BookMarks.Forum_Board_Id and Forum_User_Name=@UserId
	ORDER BY Forum_BookMark_Id desc
	end
return
GO

-- ===== List Page Procdure =====
-- Description:Page List Page
-- Author:kid
-- ==============================
CREATE PROCEDURE ListPageDays
(
@fbId int=1, 
@PageNow int=1,
@PageSize int=1,
@TopNum int=0,
@Days int=2
) 
WITH ENCRYPTION
AS 
set nocount on
if @PageNow>1 
	begin
	declare @InfoId int
	declare @MaxId int
	select @MaxId=((@PageNow-1)*@PageSize-@TopNum)
	declare FastRead cursor scroll for
	select Forum_Info_Times from Forum_Info where Forum_Info_BoardId=@fbId and 
	Forum_Info_IsTop=0 and Forum_Info_ParentId=0 and datediff(day,Forum_Info_RevertDate,getdate())<@Days 
	order by Forum_Info_Times desc
    	
	open FastRead
	fetch absolute @MaxId from FastRead into @InfoId 
	set rowcount @PageSize
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock,Forum_Info_IsVote,Forum_Info_VoteId  from Forum_Info where Forum_Info_BoardId=@fbId
	 and Forum_Info_ParentId=0 and Forum_Info_IsTop=0 and Forum_Info_Times < @InfoId 
	 and Forum_Info_RootId is not null and IsDel='0' and datediff(day,Forum_Info_RevertDate,getdate())<@Days 
	ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
   	
	close FastRead 
    	deallocate FastRead 
	end
else	
	begin
	set rowcount @PageSize 
	select Forum_Info_Id,Forum_Info_BoardId,Forum_Info_Title,Forum_Info_Users
	,Forum_Info_Revert,Forum_Info_Hit,Forum_Info_RevertDate,forum_Info_revertusers
	,Forum_Info_IsGood,Forum_Info_IsTop,Forum_Info_IsLock,Forum_Info_IsVote,Forum_Info_VoteId from Forum_Info where 
	Forum_Info_BoardId=@fbId and Forum_Info_ParentId=0 
	and Forum_Info_RootId is not null and IsDel='0' and datediff(day,Forum_Info_RevertDate,getdate())<@Days 
	ORDER BY Forum_Info_IsTop desc,Forum_Info_Times desc,Forum_Info_Id desc
	end
return
GO




-- ===== Small Papaer Procdure =====
-- Description:Page Small Paper
-- 
-- =================================
CREATE PROCEDURE SmallPaper
(
@PageNow int=1,
@PageSize int=1,
@FbId int=1
) 
WITH ENCRYPTION
AS 
set nocount on
if @PageNow>1 
	begin
	declare @PaperId int
	declare @MaxId int
	select @MaxId=((@PageNow-1)*@PageSize)
	declare FastRead cursor scroll for
	select Forum_SmallPaper_Id from Forum_SmallPaper where Forum_SmallPaper_BoardId=@FbId 
	order by Forum_SmallPaper_Id desc
    	open FastRead
	fetch absolute @MaxId from FastRead into @PaperId 
	set rowcount @PageSize
	select Forum_SmallPaper_Id,
	Forum_SmallPaper_Id,
	Forum_SmallPaper_BoardId,
	Forum_SmallPaper_UserName,
	Forum_SmallPaper_Title,
	Forum_SmallPaper_Addtime,
	Forum_SmallPaper_Hits,
	Forum_SmallPaper_Content
	from Forum_SmallPaper where Forum_SmallPaper_BoardId=@FbId 
	and Forum_SmallPaper_Id < @PaperId ORDER BY Forum_SmallPaper_Id desc
   	
	close FastRead 
    	deallocate FastRead 
	end
else	
	begin
	set rowcount @PageSize 
	select Forum_SmallPaper_Id,
	Forum_SmallPaper_Id,
	Forum_SmallPaper_BoardId,
	Forum_SmallPaper_UserName,
	Forum_SmallPaper_Title,
	Forum_SmallPaper_Addtime,
	Forum_SmallPaper_Hits,
	Forum_SmallPaper_Content
	from Forum_SmallPaper where Forum_SmallPaper_BoardId=@FbId 
	ORDER BY Forum_SmallPaper_Id desc
	end
return

GO