cs_thread_prevnext.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE procedure [dbo].cs_Thread_PrevNext
(
	@PostID int,
	@NavigationType int,
	@SettingsID int
)
AS

DECLARE @ThreadDate datetime
DECLARE	@SectionID int
DECLARE	@ThreadID int


-- Get thread info
SELECT
	@ThreadDate = T.StickyDate,
	@SectionID = T.SectionID
FROM
	cs_Threads T INNER JOIN cs_Posts P ON T.ThreadID = P.ThreadID
WHERE
	P.PostID = @PostID AND
	P.SettingsID = @SettingsID


-- Compute prev/next thread
IF @NavigationType = 1	-- previous thread
BEGIN
	SELECT TOP 1
		@ThreadID = T.MostRecentPostID
	FROM
		cs_Threads T
	WHERE
		T.SectionID = @SectionID AND
		T.IsApproved = 1 AND
		StickyDate < @ThreadDate AND
		T.SettingsID = @SettingsID
	ORDER BY
		IsSticky DESC,
		StickyDate DESC

	IF @@ROWCOUNT < 1
	BEGIN
		-- reached the beginning, no previous, so find last thread
		SELECT TOP 1
			@ThreadID = T.MostRecentPostID
		FROM
			cs_Threads T
		WHERE
			T.SectionID = @SectionID AND
			T.IsApproved = 1 AND
			T.SettingsID = @SettingsID
		ORDER BY
			IsSticky DESC,
			StickyDate DESC

		-- no threads exist
		IF @@ROWCOUNT < 1
			SET @ThreadID = 0
	END
END
ELSE IF @NavigationType = 0	-- next thread
BEGIN
	SELECT TOP 1
		@ThreadID = T.MostRecentPostID
	FROM
		cs_Threads T
	WHERE
		T.SectionID = @SectionID AND
		T.IsApproved = 1 AND
		StickyDate > @ThreadDate AND
		T.SettingsID = @SettingsID
	ORDER BY
		IsSticky ASC,
		StickyDate ASC

	IF @@ROWCOUNT < 1
	BEGIN
		-- reached the end, no next, so find first thread
		SELECT TOP 1
			@ThreadID = T.MostRecentPostID
		FROM
			cs_Threads T
		WHERE
			T.SectionID = @SectionID AND
			T.IsApproved = 1 AND
			T.SettingsID = @SettingsID
		ORDER BY
			IsSticky ASC,
			StickyDate ASC

		-- no threads exist
		IF @@ROWCOUNT < 1
			SET @ThreadID = 0
	END
END

SELECT ThreadID = @ThreadID
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Thread_PrevNext] to public
go