SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CommentsTables]( [ID] [int] IDENTITY(1,1) NOT NULL, [Key] [nvarchar](50) NOT NULL, [TableName] [nvarchar](80) NOT NULL, [StartID] [int] NOT NULL, [EndID] [int] NOT NULL, CONSTRAINT [PK_SysTables] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --根据SourceID和key获得表名 create function [dbo].[funGetTableName] ( @SourceID int, @Key nvarchar(50) ) RETURNS nvarchar(80) as begin declare @tableName nvarchar(80); declare @tableArea int; declare @mod int; declare @Size int; set @Size = 1000; set @mod = @SourceID % @Size; if @mod > 0 set @tableArea = Cast((@SourceID-1) / @Size as int) + 1; else set @tableArea = Cast((@SourceID-1) / @Size as int); set @tableName = 'comments_' + @Key + Cast(@tableArea as nvarchar(10)); return @tableName end GO --评论写入调用存储过程 CREATE proc [dbo].[procAddComment] ( @ParentID int, @SourceID int, @NickName nvarchar(20), @Content nvarchar(300), @IP nvarchar(30), @City nvarchar(30), @BeFiltered bit, @Disable bit, @Key nvarchar(50), @InsertedID int Output ) as begin declare @tableName nvarchar(80); declare @tableArea int; declare @mod int; declare @Size int; set @Size = 1000; set @mod = @SourceID % @Size; if @mod > 0 set @tableArea = Cast((@SourceID-1) / @Size as int) + 1; else set @tableArea = Cast((@SourceID-1) / @Size as int); set @tableName = 'comments_' + @Key + Cast(@tableArea as nvarchar(10)); if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName) begin declare @StartID int; declare @EndID int; set @EndID = @tableArea * @Size; set @StartID = @EndID - (@Size-1); --创建表 declare @CreateSQL nvarchar(MAX); set @CreateSQL = 'Create table [dbo].['+@tableName+']( [ID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL, [SourceID] [int] NOT NULL, [NickName] [nvarchar](20) NOT NULL, [Content] [nvarchar](300) NOT NULL, [Datetime] [datetime] NOT NULL, [IP] [nvarchar](30) NOT NULL, [City] [nvarchar](30) NOT NULL, [BeFiltered] [bit] NOT NULL, [Disable] [bit] NOT NULL, [Lou] [int] NOT NULL, [Ding] [int] NOT NULL, [Cai] [int] NOT NULL, CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]' EXEC(@CreateSQL); --创建索引 ID DESC EXEC(' CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+'] ( [ID] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'); --创建索引 Ding DESC EXEC(' CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+'] ( [Ding] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'); --创建索引 SourceID DESC EXEC(' CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+'] ( [SourceID] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'); --创建索引 Lou DESC EXEC(' CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Lou_DESC] ON [dbo].['+@tableName+'] ( [Lou] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]') --创建默认值 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_ParentID] DEFAULT ((0)) FOR [ParentID]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Datetime] DEFAULT (getdate()) FOR [Datetime]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_BeFiltered] DEFAULT ((0)) FOR [BeFiltered]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Disable] DEFAULT ((0)) FOR [Disable]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Lou] DEFAULT ((1)) FOR [Lou]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Ding] DEFAULT ((0)) FOR [Ding]'); EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Cai] DEFAULT ((0)) FOR [Cai]'); Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID); end declare @TemLou int; declare @SQL nvarchar(MAX); set @SQL = N'select @TemLou = Count(ID) from dbo.['+@tableName+N'] where SourceID=@SourceID'; exec sp_executesql @SQL, N'@SourceID int,@TemLou int output', @SourceID, @TemLou output; if @TemLou = 0 set @TemLou = 1; else set @TemLou = @TemLou + 1; declare @Lou int; set @Lou = @TemLou; declare @InsertSQL nvarchar(MAX); set @InsertSQL = N'Insert Into dbo.['+@tableName+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou]) values (@ParentID,@SourceID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();'; exec sp_executesql @InsertSQL, N'@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Lou int,@InsertedID int output', @ParentID, @SourceID, @NickName, @Content, @IP, @City, @BeFiltered, @Disable, @Lou, @InsertedID output; end GO --获得最新评论存储过程 CREATE proc [dbo].[procGetNewComments] ( @SourceID int, @Key nvarchar(50), @PageIndex int, @PageSize int, @Fields nvarchar(100), @PageCount int output ) as begin declare @tableName nvarchar(80); set @tableName = dbo.funGetTableName(@SourceID,@Key); declare @Rc int; declare @SQL nvarchar(MAX); set @SQL = N'select @Rc = COUNT(ID) from dbo.['+@tableName+N'] where SourceID = @SourceID'; exec sp_executesql @SQL, N'@SourceID int,@Rc int output', @SourceID, @Rc output; if @Rc % @PageSize > 0 set @PageCount = Cast(@Rc / @PageSize as int) + 1; else set @PageCount = Cast(@Rc / @PageSize as int); if @PageIndex = 1 begin set @SQL = N'select top '+Cast(@PageSize as nvarchar(30))+' '+@Fields + N' from dbo.['+@tableName+N'] where SourceID=@SourceID order by Lou desc'; exec sp_executesql @SQL, N'@SourceID int', @SourceID; end else begin declare @StartLou int; declare @EndLou int; --1 20 1 - 20,21- 40,41-60 set @EndLou = @Rc - (@PageIndex-1) * @PageSize; if @EndLou > @Rc set @EndLou = @Rc; set @StartLou = @EndLou - @PageSize + 1; if @StartLou =@StartLou and Lou
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
