cs_procedures.sql

解压即可使用

GO

grant execute on [dbo].cs_forums_Search to public
go
/***********************************************
* SP: cs_forums_Threads_ThreadsRead
* File Date: 2/23/2005 12:22:17 AM
***********************************************/
Print 'Creating...cs_forums_Threads_ThreadsRead'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE procedure [dbo].cs_forums_threads_ThreadsRead (
	@SectionID as int,
	@UserID as int,
	@SettingsID as int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @ReadAfter int

SELECT 
	@ReadAfter = MarkReadAfter 
FROM 
	cs_SectionsRead 
WHERE 
	UserID = @UserID AND 
	SectionID = @SectionID AND
	SettingsID = @SettingsID

IF @ReadAfter IS NOT NULL
	SELECT ThreadID = @ReadAfter
ELSE
	SELECT ThreadID = 0

IF @SectionID = -1
BEGIN
SELECT DISTINCT
	ThreadID
FROM
	cs_ThreadsRead
WHERE
	SettingsID = @SettingsID
	AND UserID = @UserID
END
ELSE
BEGIN
SELECT DISTINCT
	ThreadID
FROM
	cs_ThreadsRead
WHERE
	UserID = @UserID AND
	SectionID = @SectionID AND
	SettingsID = @SettingsID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_threads_ThreadsRead]  TO [public]
GO


/***********************************************
* SP: cs_forums_Thread_Get
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_forums_Thread_Get'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].cs_forums_Thread_Get
/*

	Procedure for getting basic information on a single thread.

*/
(
	@ThreadID int,
	@UserID int,
	@SettingsID int
) AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	T.*,
	P.PostID,
	P.Subject,
	P.Body,P.FormattedBody,
	P.PostConfiguration,
	UserName = T.PostAuthor,
	HasRead =  CASE
		WHEN @UserID = 0 THEN 0
		WHEN @UserID > 0 THEN (Select Convert(bit,Count(*)) FROM cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > p.ThreadID) or (MarkReadAfter is null and ThreadID = p.ThreadID)) and UserID = @UserID and SectionID = p.SectionID)
		END
FROM 
	cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = P.ParentID AND
	P.ThreadID = @ThreadID AND
	T.ThreadID = P.ThreadID AND
	P.UserID = U.cs_UserID and P.SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_forums_Thread_Get to public
go
/***********************************************
* SP: cs_forums_Thread_Rate_Get
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_forums_Thread_Rate_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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





create procedure [dbo].cs_Thread_Rate_Get
(
	@ThreadID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
	SELECT
		*
	FROM
		cs_vw_Users_FullUser U,
		cs_PostRating R
	WHERE
		R.UserID = U.cs_UserID AND
		R.ThreadID = @ThreadID AND U.SettingsID = @SettingsID and R.SettingsID = @SettingsID

END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on dbo.cs_Thread_Rate_Get to public
go
/***********************************************
* SP: cs_gallery_Post_GetSortOrder
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Post_GetSortOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].cs_gallery_Post_GetSortOrder
	@PostID int,
	@sqlPopulate nvarchar(4000),
	@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ThreadID int
)

CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (ThreadID)
EXEC (@sqlPopulate)

SELECT IndexID
	FROM #PageIndex I
	LEFT JOIN cs_Posts P ON P.ThreadID = I.ThreadID
	WHERE P.PostID = @PostID
		AND P.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Post_GetSortOrder to public
go
/***********************************************
* SP: cs_gallery_Search
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Search (
	@SearchSQL nvarchar(4000),
	@PageIndex int = 0,
	@PageSize int = 25,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DECLARE @StartTime datetime
	DECLARE @RowsToReturn int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @Count int

	-- Used to calculate cost of query
	SET @StartTime = GetDate()

	-- Set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	--SET ROWCOUNT @RowsToReturn

	-- Calculate the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	-- Create a temp table to store the results in
	CREATE TABLE #SearchResults
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		PostID int,
		SectionID int,
		Weight int,
		PostDate datetime
	)

	-- Fill the temp table
	INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
	exec (@SearchSQL)

	-- SELECT actual search results from this table
	SELECT
		P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
		P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
		P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
		P.PostConfiguration,
		U.*,
		T.ThreadDate,
		T.IsLocked,
		T.IsSticky, T.StickyDate, T.RatingSum, T.TotalRatings, 
		AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
		Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		IsModerator = (SELECT count(*) from cs_Moderators where UserID = P.UserID),
		HasRead = 0, -- not used,
		Convert(bit,0) as IsTracked -- Convert(bit, (Select Count(*) FROM cs_TrackedThreads where UserID = @UserID and ThreadID = P.ThreadID))
	FROM 
		cs_Posts P,
		cs_vw_Users_FullUser U,
		cs_Threads T,
		#SearchResults R
	WHERE
		P.PostID = R.PostID AND
		T.ThreadID = P.ThreadID AND
		U.cs_UserID = P.UserID AND
		R.IndexID > @PageLowerBound AND
		R.IndexID < @PageUpperBound AND
		P.SettingsID = @SettingsID and
		U.SettingsID = @SettingsID

	Select Count(*) FROM #SearchResults

	DROP Table #SearchResults

	SELECT Duration = GetDate() - @StartTime
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search to public
go
/***********************************************
* SP: cs_gallery_Search_PostReindex
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search_PostReindex'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  PROCEDURE [dbo].cs_gallery_Search_PostReindex 
(
	@RowCount int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SET ROWCOUNT @RowCount

SELECT
	T.*,
	SortOrder = 1,
	P.PostID,
	P.Subject,
	P.Body,
	P.FormattedBody,
	P.PostLevel,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	Username = P.PostAuthor,
	P.PostConfiguration,
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1)
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_Sections F
WHERE
	F.SectionID = P.SectionID AND
	F.IsActive = 1 AND
	F.IsSearchable = 1 AND 
	P.IsApproved = 1 AND
	T.ThreadID = P.ThreadID and 
	P.IsIndexed = 0 and
	F.SettingsID = @SettingsID and
	F.ApplicationType = 2 AND
	P.PostLevel = 1
ORDER BY
	T.ThreadDate DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search_PostReindex  to public
go
/***********************************************
* SP: cs_gallery_Threads_GetAllThreads
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetAllThreads'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetAllThreads
(
	@SectionID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT 
	jT.*,
	SortOrder = 1,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	cs_Threads jT
	JOIN cs_Posts jP ON jP.ThreadID = jT.ThreadID
WHERE 
	jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jT.SectionID = @SectionID AND jP.SectionID = @SectionID
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Threads_GetAllThreads to public
go
/***********************************************
* SP: cs_gallery_Threads_GetThreadSet
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetThreadSet'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate nvarchar(4000),
	@UserID int,
	@IncludeCategories bit,
	@SettingsID int,
	@TotalRecords int output
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ThreadID int
)

CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (ThreadID)
EXEC (@sqlPopulate)

SET ROWCOUNT @RowsToReturn

SELECT 
	jT.*,
	SortOrder = jPI.IndexID,
	jP.PostID, jP.ThreadID, jP.ParentID, jP.PostAuthor, jP.UserID, jP.SectionID, jP.PostLevel, jP.SortOrder, jP.Subject, jP.PostDate, jP.IsApproved,
	jP.IsLocked, jP.IsIndexed, jP.TotalViews, jP.Body, jP.FormattedBody, jP.IPAddress, jP.PostType, jP.EmoticonID, jP.SettingsID, jP.AggViews,
	jP.PropertyNames as PostPropertyNames, jP.PropertyValues as PostPropertyValues,
	jA.[FileName] as AttachmentFilename,
	HasRead = 1,
	UserName = jT.PostAuthor
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
	join cs_PostAttachments jA ON jA.PostID = jP.PostID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	AND jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter,
	AND jp.SettingsID = @SettingsID and jT.SettingsID = @SettingsID
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

Select @TotalRecords = Count(*) FROM #PageIndex

IF @IncludeCategories = 1
Begin
	Select 
		Cats.[Name], jP.PostID
	FROM 
		#PageIndex jPI
		JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
		JOIN cs_Posts jP ON jT.ThreadID = jP.ThreadID
		JOIN cs_Posts_InCategories PIC on jP.PostID = PIC.PostID
		JOIN cs_Post_Categories Cats on PIC.CategoryID = Cats.CategoryID