bbsmax.sql

这是一个关于论坛的一点小的源代码

	DELETE [bbsMax_PostIcons] WHERE IconID=@IconID
	IF @@ROWCOUNT > 0
		--BEGIN
			----更新帖子的IconID------
			--UPDATE [bbsMax_Posts] SET IconID=0 WHERE IconID=@IconID 
			RETURN (0)
		--END
	ELSE
		RETURN (1)
GO
PRINT N'正在创建 [dbo].[bbsMax_Common_GetSortOrder]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/14>
-- Description:	<获取SortOrder,（当添加数据时，表中有SortOrder字段时使用）>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_Common_GetSortOrder]
	@TableName nvarchar(256),
	@Condition nvarchar(400),--查询条件
	@SortOrder int output
AS
	SET NOCOUNT ON
	DECLARE @SQLString nvarchar(4000),@Count int
	if(@Condition<>'')
		SET @Condition=' WHERE '+@Condition
		

	SET @SQLString=N'SELECT @SortOrder=ISNULL(Max(SortOrder),0) FROM '+@TableName+@Condition
	EXECUTE sp_executesql @SQLString,N'@SortOrder int output',@SortOrder output
	SET @SortOrder=@SortOrder+1
GO
PRINT N'正在创建 [dbo].[bbsMax_GetSortOrder]'
GO
-- =============================================
-- Author:		<zzbird>
-- Create date: <2006/2/8>
-- Description:	<>
-- =============================================
CREATE FUNCTION [dbo].[bbsMax_GetSortOrder]
(
	@PostType tinyint,--1正常,2置顶,3总置顶,5待审核,4回收站
	@PostRandNumber bigint,
	@PostDate datetime
)
RETURNS bigint
AS
BEGIN

	DECLARE @ResultSortOrder bigint

	IF @PostRandNumber > 0
		SELECT @ResultSortOrder = (CAST(DATEDIFF(second, '1970-01-01 00:00:00', @PostDate) AS bigint) * 100000) + (DATEPART(millisecond, @PostDate) * 100) + @PostRandNumber % 100
	ELSE
		SELECT @ResultSortOrder = (CAST(DATEDIFF(second, '1970-01-01 00:00:00', @PostDate) AS bigint) * 100000) + (DATEPART(millisecond, @PostDate) * 100)
	
	RETURN @ResultSortOrder+@PostType*1000000000000000

END
GO
PRINT N'正在创建 [dbo].[bbsMax_IsExpired]'
GO
CREATE FUNCTION [dbo].[bbsMax_IsExpired]
(
	@ExpiresDate datetime,
	@Now datetime,
	@IsClosed bit
)
RETURNS bit
AS
BEGIN
	IF @IsClosed = 1
		RETURN 1

	IF @ExpiresDate < @Now
		RETURN 1

	RETURN 0

END
GO
PRINT N'正在创建 [dbo].[bbsMax_CurrencyExtendedPointRatio]'
GO
CREATE TABLE [dbo].[bbsMax_CurrencyExtendedPointRatio]
(
[ExtendedPointID] [tinyint] NOT NULL,
[Currency1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS_WS NULL,
[Currency2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS_WS NULL,
[Currency3] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS_WS NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_CurrencyExtendedPointRatio] 上创建主键 [PK_bbsMax_CurrencyExtendedPointRatio]'
GO
ALTER TABLE [dbo].[bbsMax_CurrencyExtendedPointRatio] ADD CONSTRAINT [PK_bbsMax_CurrencyExtendedPointRatio] PRIMARY KEY CLUSTERED  ([ExtendedPointID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateOrUpdateExtendedPointCurrencyRatio]'
GO
CREATE PROC [dbo].[bbsMax_CreateOrUpdateExtendedPointCurrencyRatio]
	@ExtendedPointIDs varchar(8000),
	@Currency1Ratios varchar(8000),
	@Currency2Ratios varchar(8000),
	@Currency3Ratios varchar(8000)
AS

	DECLARE @tbs	table(NumID INT,ID int,Currency1 nvarchar(50),Currency2 nvarchar(50),Currency3 nvarchar(50))
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Num int
	DECLARE @ID	INT
	DECLARE @Currency1 nvarchar(50)
	DECLARE @Currency2 nvarchar(50)
	DECLARE @Currency3 nvarchar(50)

	SET @Pos = 1
	SET @Num =1
	---处理ID
	WHILE(@Pos <= LEN(@ExtendedPointIDs))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @ExtendedPointIDs,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@ExtendedPointIDs) + 1
		SELECT @ID = RTRIM(LTRIM(SUBSTRING(@ExtendedPointIDs, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1
		DECLARE @TEMP TINYINT
		SET @TEMP =0
		IF NOT EXISTS(SELECT * FROM bbsMax_CurrencyExtendedPointRatio WHERE ExtendedPointID=@ID)
		BEGIN
			--如果数据库没有则插入
			INSERT INTO bbsMax_CurrencyExtendedPointRatio (ExtendedPointID,Currency1,Currency2,Currency3)
			VALUES(@ID,'0','0','0')
		END
		INSERT INTO @tbs VALUES (@Num,@ID,'0','0','0')
		SELECT @Num = @Num+1
	END

	SET @Pos = 1
	SET @Num =1
	---处理Currency1
	WHILE(@Pos <= LEN(@Currency1Ratios))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @Currency1Ratios,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@Currency1Ratios) + 1
		SELECT @Currency1 = RTRIM(LTRIM(SUBSTRING(@Currency1Ratios, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1
		UPDATE @tbs SET Currency1=@Currency1 WHERE NumID=@Num
		SELECT @Num = @Num+1
	END

	SET @Pos = 1
	SET @Num =1
	---处理Currency2
	WHILE(@Pos <= LEN(@Currency2Ratios))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @Currency2Ratios,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@Currency2Ratios) + 1
		SELECT @Currency2 = RTRIM(LTRIM(SUBSTRING(@Currency2Ratios, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1
		UPDATE @tbs SET Currency2=@Currency2 WHERE NumID=@Num
		SELECT @Num = @Num+1
	END

	SET @Pos = 1
	SET @Num =1
	---处理Currency3
	WHILE(@Pos <= LEN(@Currency3Ratios))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @Currency3Ratios,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@Currency3Ratios) + 1
		SELECT @Currency3 = RTRIM(LTRIM(SUBSTRING(@Currency3Ratios, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1
		UPDATE @tbs SET Currency3=@Currency3 WHERE NumID=@Num
		SELECT @Num = @Num+1
	END
	-----处理表变量结束
	
	UPDATE bbsMax_CurrencyExtendedPointRatio 
	SET bbsMax_CurrencyExtendedPointRatio.Currency1=a.Currency1,
		bbsMax_CurrencyExtendedPointRatio.Currency2=a.Currency2,
		bbsMax_CurrencyExtendedPointRatio.Currency3=a.Currency3
	FROM @tbs as a
	WHERE	bbsMax_CurrencyExtendedPointRatio.ExtendedPointID=a.ID
GO
PRINT N'正在创建 [dbo].[bbsMax_AttachmentExchanges]'
GO
CREATE TABLE [dbo].[bbsMax_AttachmentExchanges]
(
[AttachmentID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[Price] [int] NOT NULL,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_AttachmentExchanges] 上创建主键 [PK_bbsMax_AttachmentExchanges]'
GO
ALTER TABLE [dbo].[bbsMax_AttachmentExchanges] ADD CONSTRAINT [PK_bbsMax_AttachmentExchanges] PRIMARY KEY CLUSTERED  ([AttachmentID], [UserID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_ThreadCatalogs]'
GO
CREATE TABLE [dbo].[bbsMax_ThreadCatalogs]
(
[ThreadCatalogID] [int] NOT NULL IDENTITY(1, 1),
[ThreadCatalogName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[LogoUrl] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_ThreadCatalogs] 上创建主键 [PK_bbsMax_ThreadCatalogs]'
GO
ALTER TABLE [dbo].[bbsMax_ThreadCatalogs] ADD CONSTRAINT [PK_bbsMax_ThreadCatalogs] PRIMARY KEY CLUSTERED  ([ThreadCatalogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_UpdateThreadCatalog]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/22>
-- Description:	<修改>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_UpdateThreadCatalog] 
	@ThreadCatalogID int,
	@ThreadCatalogName nvarchar(64),
	@LogoUrl nvarchar(512)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM [bbsMax_ThreadCatalogs] WITH (NOLOCK) WHERE ThreadCatalogName=@ThreadCatalogName AND ThreadCatalogID<>@ThreadCatalogID)
			RETURN (3) 
--	ELSE IF EXISTS (SELECT * FROM [bbsMax_ThreadCatalogs] WITH (NOLOCK) WHERE LogoUrl=@LogoUrl AND ThreadCatalogID<>@ThreadCatalogID)
--			RETURN (6) 
	ELSE BEGIN
		UPDATE [bbsMax_ThreadCatalogs] SET
		[ThreadCatalogName]=@ThreadCatalogName,
		[LogoUrl]=@LogoUrl 
		WHERE ThreadCatalogID=@ThreadCatalogID
		RETURN (0)
	END
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllThreadCatalogs]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/1/29>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAllThreadCatalogs]

AS
	SET NOCOUNT ON
	SELECT * FROM bbsMax_ThreadCatalogs WHERE ThreadCatalogID<>0
	RETURN
GO
PRINT N'正在创建 [dbo].[bbsMax_Common_GetRecordsByPage_LongCondition]'
GO
CREATE PROCEDURE [dbo].bbsMax_Common_GetRecordsByPage_LongCondition
	@PageIndex int,
	@PageSize int,
	@TableName nvarchar(256),
	@SelectFields nvarchar(512) = N'*', --查询字段，默认为 *
	@Condition nvarchar(4000) = N'',     --条件例如"DirectoryID=4"
	@SortField nvarchar(256) = N'[SortOrder]',
	@IsDesc bit = 1 --是否倒序
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @SQLString nvarchar(4000);
	DECLARE @WhereString1 nvarchar(800);
	DECLARE @WhereString2 nvarchar(800);

	IF @Condition IS NULL OR @Condition = N'' BEGIN
		SELECT @WhereString1 = N'';
		SELECT @WhereString2 = N'WHERE ';
	END
	ELSE BEGIN
		SELECT @WhereString1 = N'WHERE ' + @Condition;
		SELECT @WhereString2 = N'WHERE ' + @Condition + N' AND ';
	END

	IF @PageIndex = 0 BEGIN
		SELECT @SQLString = N'SELECT TOP ' + STR(@PageSize)
			+ N' ' + @SelectFields
			+ N' FROM ' + @TableName + ' WITH (NOLOCK)
			' + @WhereString1 + '
			ORDER BY ' + @SortField;
		IF @IsDesc = 1
			SELECT @SQLString = @SQLString + ' DESC';
	END
	ELSE BEGIN
		IF @IsDesc = 1
			SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
			+ N' ' + @SelectFields
			+ N' FROM ' + @TableName+' WITH (NOLOCK) 
			' + @WhereString2 + @SortField + ' <
				(SELECT Min(' + @SortField + ') FROM 
					(SELECT TOP ' + STR(@PageSize * @PageIndex) + ' ' + @SortField + ' FROM ' + @TableName+' WITH (NOLOCK)
						' + @WhereString1 + '
							ORDER BY ' + @SortField + ' DESC) AS IDX)
			ORDER BY ' + @SortField + ' DESC';
		ELSE
			SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
			+ N' ' + @SelectFields
			+ N' FROM ' + @TableName+' WITH (NOLOCK) 
			' + @WhereString2 + @SortField + ' >
				(SELECT Max(' + @SortField + ') FROM 
					(SELECT TOP ' + STR(@PageSize * @PageIndex) + ' ' + @SortField + ' FROM ' + @TableName+' WITH (NOLOCK)
						' + @WhereString1 + '
							ORDER BY ' + @SortField + ' ASC) AS IDX)
			ORDER BY ' + @SortField + ' ASC';
	END

	EXEC (@SQLString);

END
GO
PRINT N'正在创建 [dbo].[bbsMax_GetPostMarksBySeach]'
GO
CREATE PROCEDURE [dbo].[bbsMax_GetPostMarksBySeach]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(4000),
	@ActionType int
AS
	DECLARE @Condition varchar(8000)
	-------Modify By 帅帅 2007/3/20----------start
	IF(@Keyword='') BEGIN
			SET @Condition=''
	END
	ELSE BEGIN
		IF(@ActionType=1)--操作者
			SET @Condition='UserID in('+@Keyword+')'
		ELSE IF(@ActionType=2)--日期
			SET @Condition='CreateDate LIKE ''%'+@Keyword+'%'''
		ELSE IF(@ActionType=3)--被评分的帖子
		SET @Condition='PostID in (SELECT PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE [SortOrder]<4000000000000000 AND [Subject] like ''%' + @Keyword + '%'')'
		ELSE IF(@ActionType=4)--原因
		SET @Condition='Reason LIKE ''%'+@Keyword+'%'''
	END
	EXECUTE bbsMax_Common_GetRecordsByPage_LongCondition @PageIndex,@PageSize,'bbsMax_PostMarks','*','','PostMarkID',1
	IF(@Condition<>'')
		EXEC('SELECT COUNT(1) TotalRecord FROM bbsMax_PostMarks WHERE ' +@Condition) 
	ELSE
		EXEC('SELECT COUNT(1) TotalRecord FROM bbsMax_PostMarks')
GO
PRINT N'正在创建 [dbo].[bbsMax_UpdateAnnouncement]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/1/11>
-- Description:	<修改公告>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_UpdateAnnouncement]
	@AnnouncementID int,
	@AnnouncementType tinyint,
	@Subject nvarchar(200),
	@Content ntext,
	@StartDate datetime,