cs_procedures.sql

解压即可使用

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

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate DESC

-- Select the individual posts
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,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	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(UserID) from cs_Moderators where UserID = @UserID),
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.cs_UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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






CREATE        PROCEDURE [dbo].cs_forums_Post
/*

	Procedure for getting basic information on a single post.

*/
(
	@PostID int,
	@UserID int,
	@TrackViews bit,
	@SettingsID int
) AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int 
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit

SELECT 
	@ThreadID = ThreadID, 
	@SectionID = SectionID, 
	@SortOrder=SortOrder,
	@IsApproved = IsApproved
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Is the Forum 0 (If so this is a private message and we need to verify the user can view it
IF @SectionID = 0
BEGIN
	IF NOT EXISTS (SELECT UserID FROM cs_PrivateMessages WHERE UserID = @UserID AND ThreadID = @ThreadID)
		RETURN
END

-- Get the previous and next thread id
EXEC cs_Thread_PrevNext @ThreadID, @SectionID, @SettingsID, @NextThreadID OUTPUT, @PrevThreadID OUTPUT

DECLARE @TrackingThread bit

IF @TrackViews = 1
BEGIN
	-- Update the counter for the number of times this post is viewed
	UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
	UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID and SettingsID = @SettingsID
END

-- If @UserID is 0 the user is anonymous
IF @UserID > 0 AND @IsApproved = 1
BEGIN

	-- Mark the post as read
	-- *********************
	IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID)
		INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)

END

-- get the anonymous user id for this site
if( @UserID = 0 ) 
BEGIN
	exec cs_GetAnonymousUserID @SettingsID, @UserID output
END


IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
	SELECT @TrackingThread = 1
ELSE
	SELECT @TrackingThread = 0

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.*, P.PostAuthor as [Username],
	T.ThreadDate,
	T.StickyDate,
	T.IsLocked,
	T.IsSticky,
	T.RatingSum,
	T.TotalRatings,
	HasRead = 0,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	IndexInThread = (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID),
	Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	PrevThreadID = @PrevThreadID,
	NextThreadID = @NextThreadID,
	UserIsTrackingThread = @TrackingThread
FROM 
	cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = @PostID AND
	P.ThreadID = T.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_Post to public
go
/***********************************************
* SP: cs_forums_Posts_PostSet
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_forums_Posts_PostSet'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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





CREATE         PROCEDURE [dbo].cs_forums_Posts_PostSet
(
	@PostID	int,
	@PageIndex int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@UserID int,
	@ReturnRecordCount bit,
	@AllowUnapproved bit = 0,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
DECLARE @SectionID int

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

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

-- Get the ThreadID
SELECT
	@ThreadID = ThreadID,
	@SectionID = SectionID
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Is the Forum 0 (If so this is a private message and we need to verify the user can view it
IF @SectionID = 0
BEGIN
	IF NOT EXISTS (SELECT UserID FROM cs_PrivateMessages WHERE UserID = @UserID AND ThreadID = @ThreadID AND SettingsID = @SettingsID)
		RETURN
END

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

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY PostDate DESC

-- Sort by Author
IF @SortBy = 1 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID

ELSE IF @SortBy = 1 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID DESC

-- Sort by SortOrder
IF @SortBy = 2 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder

ELSE IF @SortBy = 2 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder DESC

-- Select the individual posts
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,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = 0, --not used(SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	IsModerator = 0, -- not used
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.cs_UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END

IF @ReturnRecordCount = 1
  SELECT count(PostID) FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID  and SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_forums_Posts_PostSet to public
go
/***********************************************
* SP: cs_Forums_ReAssignAndDeleteUser
* File Date: 4/12/2005 4:34:36 PM
***********************************************/
Print 'Creating...cs_Forums_ReAssignAndDeleteUser'

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


Create Proc [dbo].cs_Forums_ReAssignAndDeleteUser

(
	@CurrentUserName nvarchar(256),
	@NewUserName nvarchar(256),
	@SettingsID int
)

as

Declare @CUserID int
Declare @NUserID int
Declare @CGUserID uniqueidentifier
Declare @NGUserID uniqueidentifier

Declare @ApplicationID uniqueidentifier


--Find the Application
Select @ApplicationID = ApplicationID FROM aspnet_applications where ApplicationName in (Select ApplicationName FROM cs_SiteSettings where SettingsID = @SettingsID)

if(@ApplicationID is null)
Return -1

--Find the Guid UserID's
Select @CGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @CurrentUserName
Select @NGUserID = UserID FROM aspnet_Users where  ApplicationID = @ApplicationID and UserName = @NewUserName

if(@CGUserID is null)
Return -2

if(@NGUserID is null)
Return -3

--Find the int UserID's
Select @CUserID = UserID FROM cs_UserProfile where MembershipID = @CGUserID and SettingsID = @SettingsID
Select @NUserID = UserID FROM cs_UserProfile where MembershipID = @NGUserID and SettingsID = @SettingsID

if(@CUserID is null)
Return -4

if(@NUserID is null)
Return -5

-- ReAssign Content

update cs_ModerationAudit
Set UserID = @NUserID 
where UserID = @CUserID

update cs_PostAttachments
Set UserID = @NUserID
where UserID = @CUserID


update cs_Posts
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_PrivateMessages
Set UserID = @NUserID
where UserID = @CUserID

update cs_Threads
Set UserID = @NUserID, PostAuthor = convert(nvarchar(64), @NewUserName)
where UserID = @CUserID

update cs_Threads
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

update cs_Sections
Set MostRecentPostAuthorID = @NUserID, MostRecentPostAuthor = convert(nvarchar(64), @NewUserName)
where MostRecentPostAuthorID = @CUserID

-- Delete Content

Delete FROM cs_PostRating where UserID = @CUserID
Delete FROM cs_SectionsRead where UserID = @CUserID
Delete FROM cs_statistics_User where UserID = @CUserID
Delete FROM cs_TrackedSections where UserID = @CUserID
Delete FROM cs_ThreadsRead where UserID = @CUserID
Delete FROM cs_UserAvatar where UserID = @CUserID
Delete FROM cs_Votes where UserID = @CUserID
Delete FROM cs_UserProfile where UserID = @CUserID

Delete FROM aspnet_UsersInRoles where UserID = @CGUserID
Delete FROM aspnet_Profile where UserID = @CGUserID
Delete FROM aspnet_Membership where UserID = @CGUserID
Delete FROM aspnet_Users where UserID = @CGUserID


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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_forums_Search (
	@SearchSQL nvarchar(4000),
	--@RecordCountSQL 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
	)


	Declare @TotalResults int

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

	Set @TotalResults = @@rowcount

	-- 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,
		AttachmentFilename = '', --ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
		Replies = 0, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		IsModerator = 0, --(SELECT count(*) from cs_Moderators where UserID = P.UserID),
		HasRead = 0 -- not used
	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

	-- Do we need to return a record estimate?
	--exec (@RecordCountSQL)

	Select @TotalResults

	--DROP Table #SearchResults

	SELECT Duration = GetDate() - @StartTime
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON