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
