📄 idmax.sql

📁 这是一个关于论坛的一点小的源代码
💻 SQL
📖 第 1 页 / 共 5 页
字号:
			ELSE
				SET @GetFromLast=0
	-------------------------------------------------
	-------------------------------------------------
			IF @GetFromLast=1 BEGIN
				IF @PageIndex=@TotalPage-1 BEGIN
					IF @ResidualCount=0
						SET @ResidualCount=@PageSize;
					SELECT @SQLString = N'SELECT ' + STR(@ResidualCount)
						+ N' ' + @SelectFields
						+ N' FROM ' + @TableName + ' WITH (NOLOCK)
						' + @WhereString1 + '
						ORDER BY ' + @SortField;
					IF @IsDesc = 0--反过来
						SELECT @SQLString = @SQLString + ' DESC';
					EXEC (@SQLString);
					RETURN 1
				END 
				ELSE IF  @PageIndex>@TotalPage-1 BEGIN --已经超过最大页数
					SELECT @SQLString = N'SELECT TOP ' + @SelectFields
						+ N' FROM ' + @TableName + ' WITH (NOLOCK) WHERE 1=2'
					EXEC (@SQLString);
					RETURN 0
				END
				ELSE BEGIN
					SET @PageIndex=@TotalPage-(@PageIndex+1)---
					IF @IsDesc=1
						SET @IsDesc=0
					ELSE
						SET @IsDesc=1
					SET @ResetOrder=1
				END  
			END
			ELSE 
				SET @ResetOrder=0
		END
		
		DECLARE @TopCount INT
		IF @GetFromLast=1
			SET @TopCount=@PageSize * (@PageIndex-1)+@ResidualCount
		ELSE
			SET @TopCount=@PageSize * @PageIndex
	-------------------------------------------------
		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(@TopCount) + ' ' + @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(@TopCount) + ' ' + @SortField + ' FROM ' + @TableName+' WITH (NOLOCK)
						' + @WhereString1 + '
							ORDER BY ' + @SortField + ' ASC) AS IDX)
			ORDER BY ' + @SortField + ' ASC';
	END
	EXEC (@SQLString);
	RETURN @ResetOrder

END
GO
PRINT N'正在创建 [dbo].[Max_NewUsersLogs]'
GO
CREATE TABLE [dbo].[Max_NewUsersLogs]
(
[LogYear] [int] NOT NULL,
[LogMonth] [int] NOT NULL,
[LogDay] [int] NOT NULL,
[LogHour] [int] NOT NULL,
[NewUsers] [int] NOT NULL CONSTRAINT [DF_Max_NewUsersLogs_NewUsers] DEFAULT ((0))
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_NewUsersLogs] 上创建主键 [PK_Max_NewUsersLogs]'
GO
ALTER TABLE [dbo].[Max_NewUsersLogs] ADD CONSTRAINT [PK_Max_NewUsersLogs] PRIMARY KEY CLUSTERED  ([LogYear], [LogMonth], [LogDay], [LogHour]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Common_GetSortOrder]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/14>
-- Description:	<获取SortOrder,（当添加数据时，表中有SortOrder字段时使用）>
-- =============================================
CREATE PROCEDURE [dbo].Max_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].[Max_Medals]'
GO
CREATE TABLE [dbo].[Max_Medals]
(
[MedalID] [int] NOT NULL IDENTITY(1, 1),
[MedalName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[LogoUrl] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[IsEnabled] [bit] NOT NULL,
[MappedRoleID] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_Medals] 上创建主键 [PK_Max_Medals]'
GO
ALTER TABLE [dbo].[Max_Medals] ADD CONSTRAINT [PK_Max_Medals] PRIMARY KEY CLUSTERED  ([MedalID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DeleteMedal]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/18>
-- Description:	<删除一个勋章>
-- =============================================
CREATE PROCEDURE [dbo].[Max_DeleteMedal]
	@MedalID int
AS
	SET NOCOUNT ON
	DELETE [Max_Medals] WHERE MedalID=@MedalID
	IF @@ROWCOUNT > 0
		RETURN (0)
	ELSE
		RETURN (1)
GO
PRINT N'正在创建 [dbo].[Max_GetPasswordRecoveryLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetPasswordRecoveryLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(64),
	@ActionType tinyint
AS
	DECLARE @Condition nvarchar(4000)
	IF(@Keyword='')
		SET @Condition ='';
	ELSE
		BEGIN
			IF(@ActionType=1)--按用户名或用户昵称
				SET @Condition = 'UserID In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=2)
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_PasswordRecoveryLogs','*',@Condition,'LogID',1
	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_PasswordRecoveryLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_PasswordRecoveryLogs')
GO
PRINT N'正在创建 [dbo].[Max_MoveContacts]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/11>
-- Description:	<批量移动联系人到另一组>
-- =============================================
CREATE PROCEDURE [dbo].[Max_MoveContacts] 
	@GroupID int,
	@NewGroupID int,
	@UserID int,
	@ContactIdentities ntext
AS
	SET NOCOUNT ON
	--
	IF(@GroupID<>0)
		BEGIN
			IF(EXISTS (SELECT * FROM [Max_ContactGroups] WITH (NOLOCK) WHERE GroupID=@NewGroupID and UserID=@UserID))
			BEGIN
				EXEC('Update [Max_Contacts] SET GroupID = ' + @NewGroupID + ' WHERE ContactID IN ('+ @ContactIdentities +') and GroupID = '+@GroupID)
				RETURN (0)
			END
		END
	ELSE
	BEGIN
	--	
	IF (EXISTS (SELECT * FROM [Max_ContactGroups] WITH (NOLOCK) WHERE GroupID=@GroupID and UserID=@UserID) AND 
	    EXISTS (SELECT * FROM [Max_ContactGroups] WITH (NOLOCK) WHERE GroupID=@NewGroupID and UserID=@UserID))
	BEGIN
		EXEC('Update [Max_Contacts] SET GroupID = ' + @NewGroupID + ' WHERE ContactID IN ('+ @ContactIdentities +') and GroupID = '+@GroupID)
		RETURN (0)
	END
	ELSE
	RETURN (-1)
	END
GO
PRINT N'正在创建 [dbo].[Max_CreateIPAddress]'
GO
CREATE PROCEDURE [dbo].[Max_CreateIPAddress]
	@StartIP bigint,
	@EndIP bigint,
	@AddressTop nvarchar(64),
	@AddressEnd nvarchar(64)
AS
	INSERT INTO [dbo].[Max_IpAddresses] (
			[StartIP],
			[EndIP],
			[Location1],
			[Location2]
			) VALUES (
			@StartIP,
			@EndIP,
			@AddressTop,
			@AddressEnd
			)
	RETURN
GO
PRINT N'正在创建 [dbo].[Max_UserInvites]'
GO
CREATE TABLE [dbo].[Max_UserInvites]
(
[UserID] [int] NOT NULL,
[InvitedCount] [int] NOT NULL,
[SpareInviteCount] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_UserInvites] 上创建主键 [PK_Max_UserInvites]'
GO
ALTER TABLE [dbo].[Max_UserInvites] ADD CONSTRAINT [PK_Max_UserInvites] PRIMARY KEY CLUSTERED  ([UserID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_RenameContactGroup]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/11>
-- Description:	<给指定的分组重命名>
-- =============================================
CREATE PROCEDURE [dbo].[Max_RenameContactGroup]
	@GroupID int,
	@UserID int,
	@NewGroupName Nvarchar(56)
AS
	SET NOCOUNT ON 
	IF EXISTS(SELECT * FROM [Max_ContactGroups] where GroupID=@GroupID AND UserID=@UserID)
	BEGIN
		UPDATE [Max_ContactGroups] SET GroupName=@NewGroupName WHERE GroupID=@GroupID
		RETURN (0)
	END
	ELSE
		RETURN (-1)
GO
PRINT N'正在创建 [dbo].[Max_GetChangePasswordLogsBySearch]'
GO
CREATE PROCEDURE [dbo].[Max_GetChangePasswordLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(64),
	@ActionType tinyint
AS
	DECLARE @Condition nvarchar(4000)
	IF(@Keyword='')
		SET @Condition ='';
	ELSE
		BEGIN
			IF(@ActionType=1)--按用户名或用户昵称
				SET @Condition = 'UserID In (SELECT u.UserID FROM Max_Users u inner join Max_UserProfiles p on u.UserID=p.UserID Where u.UserName like ''%'+@Keyword+'%'' or p.NickName like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=2)--更改之前密码
				SET @Condition ='OldPassword like ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=3)--更改日期
				SET @Condition = 'CreateDate like ''%'+@Keyword+'%'''
		END
	EXECUTE Max_Common_GetRecordsByPage @PageIndex,@PageSize,'Max_ChangePasswordLogs','*',@Condition,'LogID',1
	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ChangePasswordLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM Max_ChangePasswordLogs')
GO
PRINT N'正在创建 [dbo].[Max_DefaultEmoticons]'
GO
CREATE TABLE [dbo].[Max_DefaultEmoticons]
(
[EmoticonID] [int] NOT NULL IDENTITY(1, 1),
[Shortcut] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[FileName] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_DefaultEmoticons] 上创建主键 [PK_Max_DefaultEmoticons]'
GO
ALTER TABLE [dbo].[Max_DefaultEmoticons] ADD CONSTRAINT [PK_Max_DefaultEmoticons] PRIMARY KEY CLUSTERED  ([EmoticonID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DefaultEmoticons] 的索引 [IX_Max_DefaultEmoticons_Shortcut]'
GO
CREATE NONCLUSTERED INDEX [IX_Max_DefaultEmoticons_Shortcut] ON [dbo].[Max_DefaultEmoticons] ([Shortcut]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_DefaultEmoticons] 的索引 [IX_Max_DefaultEmoticons_SortOrder]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_DefaultEmoticons_SortOrder] ON [dbo].[Max_DefaultEmoticons] ([SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_GetAllDefaultEmoticons]'
GO
-- =============================================
-- Author:		sek
-- Create date: 2007/5/21
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Max_GetAllDefaultEmoticons]
AS
	SET NOCOUNT ON
	SELECT * FROM [Max_DefaultEmoticons] WITH(NOLOCK) ORDER BY SortOrder
GO
PRINT N'正在创建 [dbo].[Max_Common_DoSortOrder]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/18>
-- Description:	<排序>
-- =============================================
CREATE PROCEDURE [dbo].Max_Common_DoSortOrder 
	@SortOrder1 int,
	@SortOrder2 int,--要移动的记录（SortOrder为@SortOrder2的记录排在@SortOrder1的后面，@SortOrder1为-1时排在最前面）
	@GroupColumn nvarchar(256),--分组字段（例如，"GroupID","DirectoryID"）,没有分组时用''表示
	@GroupID int,--分组ID值
	@TableName nvarchar(256)--表名
AS
	SET NOCOUNT ON 
	DECLARE @SQLString nvarchar(4000),@GroupCondition nvarchar(512),@TempOrder int
	IF(@GroupColumn<>'')
		SET @GroupCondition=' and '+@GroupColumn+'='+STR(@GroupID)
	ELSE
		SET @GroupCondition=''
	DECLARE @e1 int,@e2 int,@e3 int,@e4 int
	BEGIN TRANSACTION 
	SET @SQLString=N'SELECT @TempOrder=ISNULL(Max(SortOrder),0) FROM '+@TableName+' WITH (NOLOCK) where 1=1'+@GroupCondition
	EXECUTE sp_executesql @SQLString,N'@TempOrder int output',@TempOrder output
	SELECT @e1=@@error
	SET @TempOrder=@TempOrder+1--获取最大的SortOrder加1作为临时值
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder='+str(@TempOrder)+' WHERE SortOrder='+STR(@SortOrder2)+@GroupCondition
	EXEC(@SQLString)--把SortOrder为@SortOrder2的值改为@TempOrder
	SELECT @e2=@@error
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder=SortOrder+1 WHERE SortOrder>'+STR(@SortOrder1)+'AND SortOrder<'+STR(@SortOrder2)+@GroupCondition
	EXEC(@SQLString)--把大于@SortOrder1小于@SortOrder2的值全部加1
	SELECT @e3=@@error
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder='+STR(@SortOrder1)+'+1 WHERE SortOrder='+str(@TempOrder)+@GroupCondition
	EXEC(@SQLString)--把原来值为@SortOrder2的值改为@SortOrder1加1
	SELECT @e4=@@error
	
	IF(@e1=0 AND @e2=0 AND @e3=0 AND @e4=0)
		COMMIT TRANSACTION
	ELSE
		ROLLBACK TRANSACTION
GO
PRINT N'正在创建 [dbo].[Max_LinkedApplications]'
GO
CREATE TABLE [dbo].[Max_LinkedApplications]
(
[ApplicationID] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Url] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[DisplayName] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_LinkedApplications] 上创建主键 [PK_Max_LinkedApplications]'
GO
ALTER TABLE [dbo].[Max_LinkedApplications] ADD CONSTRAINT [PK_Max_LinkedApplications] PRIMARY KEY CLUSTERED  ([ApplicationID], [Url]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_GetAllLinkedApplications]'
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Max_GetAllLinkedApplications]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM [Max_LinkedApplications] WITH(NOLOCK)
END
GO
PRINT N'正在创建 [dbo].[Max_GetSystemMessages]'
GO
----帅帅 2007/9/12
CREATE PROCEDURE [dbo].[Max_GetSystemMessages]
AS
IF EXISTS(
SELECT * FROM Max_SystemMessages 
WHERE EndTime>getdate() 
)
💿 文件大小 3572 K
👤 上传用户 feiguohaihu
📂 所属分类 SCSI/ASPI
🏷️ 相关标签

#论坛 #源代码
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -