cs_guestbook_post_create.prc

community server 源码

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
BEGIN


	INSERT cs_Threads 	
	( 
		SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID 
	)
	VALUES
	( 
		@SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@PostAuthor,
		@UserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		0,
		1,
		0,
		@PostDate,
		-1,
		@SettingsID 
	)

	-- Get the new ThreadID
	SELECT 
		@ThreadID = @@IDENTITY
	FROM
		cs_Threads
		
	-- Now we add the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 

		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID
	        )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, which we don't know until after INSERT
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Subject, 
		@UserID, 
		@PostAuthor,
		1, 
		0, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostDate, 
		@UserHostAddress, 
		-1,
		@PropertyNames,
		@PropertyValues,
		@SettingsID )
		

	-- Get the new PostID
	SELECT 
		@PostID = @@IDENTITY

	-- Update the new Thread with the new PostID
	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostID
	WHERE 
		ThreadID = @ThreadID

END
ELSE BEGIN	-- @ParentID <> 0 means there is a reply to an existing post

	-- Insert the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 
		1, 
		0,
		@Subject, 
		@UserID, 
		@PostAuthor, 
		1, 
		0, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostDate, 
		@UserHostAddress, 
		-1,
		@PropertyNames,
		@PropertyValues,
		@SettingsID  )


		-- Grab the new PostID and update the ThreadID's info
		SELECT 
			@PostID = @@IDENTITY 




		UPDATE
			cs_Threads 	
		SET 
			TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
		WHERE
			ThreadID = @ThreadID
	END
END


COMMIT TRAN

SET NOCOUNT OFF

SELECT @PostID = @PostID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





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