cs_procedures.sql

解压即可使用

	WHERE 
		jPI.IndexID > @PageLowerBound
		AND jPI.IndexID < @PageUpperBound
		AND jP.PostLevel = 1 and jT.SettingsID = @SettingsID and jP.SettingsID = @SettingsID
End

DROP TABLE #PageIndex

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


Create Proc [dbo].cs_GetAnonymousUserID
(
	@SettingsID int,
	@UserID int output
)
as
SET Transaction Isolation Level Read UNCOMMITTED
Select @UserID = cs_UserID FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and IsAnonymous = 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 

GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  procedure [dbo].cs_GetSectionSubscriptionType
(
	@UserID int,
	@SectionID int,
	@SettingsID int,
	@SubType int OUTPUT
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT SubscriptionType FROM cs_TrackedSections WHERE SectionID=@SectionID AND UserID=@UserID and SettingsID = @SettingsID








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_GetTotalPostCount
(
	@SettingsID int
)

 AS
SET Transaction Isolation Level Read UNCOMMITTED
	SELECT TOP 1 
		TotalPosts 
	FROM 
		forums_Statistics where SettingsID = @SettingsID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE         PROCEDURE [dbo].cs_GetUserIDByAppToken
(
	@AppUserToken varchar(128),
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
SELECT
	U.cs_UserID
FROM 
	cs_vw_Users_FullUser U (nolock)
WHERE 
	U.cs_AppUserToken = @AppUserToken and SettingsID = @SettingsID






























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE  PROCEDURE [dbo].cs_Groups_Get
(
	@SettingsID int,
	@ApplicationType smallint = 0,
	@RequireModeration bit = 0

)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF (@RequireModeration = 0)
		SELECT
			*
		FROM
			cs_Groups
		WHERE
			(SettingsID = @SettingsID) and ApplicationType = @ApplicationType
	ELSE
		SELECT DISTINCT
			FG.*
		FROM
			cs_Sections S,
			cs_Groups FG
		WHERE
			S.GroupID = FG.GroupID AND
			S.IsActive = 1 AND
			(SELECT Count(PostID) FROM cs_Posts P WHERE SectionID = S.SectionID AND P.IsApproved = 0) > 0 AND
			(FG.SettingsID = @SettingsID) AND
			FG.ApplicationType = @ApplicationType

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE       PROCEDURE [dbo].cs_Group_CreateUpdateDelete
(
	@GroupID	int out,
	@Name		nvarchar(256),
	@ApplicationType smallint = 0,
	@Action 	int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	DECLARE @SortOrder int

	SELECT @SortOrder = coalesce(MAX(SortOrder) + 1, 1) FROM cs_Groups where SettingsID = @SettingsID

	-- Create a new forum group
	INSERT INTO 
		cs_Groups 
		(
			Name,
			SortOrder,
			ApplicationType,
			SettingsID
		)
	VALUES 
		(
			@Name,
			@SortOrder,
			@ApplicationType,
			@SettingsID
		)
	
	SET @GroupID = @@IDENTITY
END


-- UPDATE
ELSE IF @Action = 1
BEGIN

	IF EXISTS(SELECT GroupID FROM cs_Groups WHERE GroupID = @GroupID)
	BEGIN
		UPDATE
			cs_Groups
		SET
			Name = @Name
		WHERE
			GroupID = @GroupID and SettingsID = @SettingsID
	END

END

-- DELETE
ELSE IF @Action = 2
BEGIN
	DELETE cs_Groups WHERE GroupID = @GroupID and SettingsID = @SettingsID
END


























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE         PROCEDURE [dbo].cs_guestbook_GetPosts
(
	@PageIndex int,
	@PageSize int,
	@SortOrder bit,
	@SectionID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID 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


-- 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  @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID

ELSE 
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID 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 = '',
	Replies = 0,
	IsModerator = 0,
	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


  SELECT Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID

DROP Table #PageIndex


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_guestbook_Post_Create
(
	@SectionID int,
	@Subject nvarchar(256),
	@UserID int,
	@PostAuthor nvarchar(64) = null,
	@Body ntext,
	@FormattedBody ntext,
	@EmoticonID int = 0,
	@PostType int = 0,
	@PostDate datetime = null,
	@UserHostAddress nvarchar(32),
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int,
	@PostID int out
) 
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
SET NOCOUNT ON
DECLARE @ThreadID int

-- set the PostDate
IF @PostDate IS NULL
	SET @PostDate = GetDate()

-- set the username
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser 
	WHERE 
		cs_UserID = @UserID

-- Do we care about duplicates?

Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID


SET NOCOUNT ON
BEGIN TRAN


IF @ThreadID is null
B