Highly visited comment system database stored procedure architecture
- 大家讲道理Original
- 2016-11-10 11:16:571119browse
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 < 1
set @StartLou = 1;
set @SQL = N'select '+@Fields + N' from dbo.['+@tableName+N'] where Lou>=@StartLou and Lou<=@EndLou and SourceID = @SourceID order by Lou desc';
exec sp_executesql @SQL,
N'@SourceID int,@StartLou int,@EndLou int',
@SourceID,@StartLou,@EndLou;
end
end
GO
--踩
Create proc [dbo].[procCai]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
declare @tableName nvarchar(80);
set @tableName = dbo.funGetTableName(@SourceID,@key);
declare @SQL nvarchar(MAX);
set @SQL = N'update dbo.['+@tableName+N'] set Cai=Cai+1 where ID=@ID;select @Times=Cai from dbo.['+@tableName+N'] where ID=@ID';
exec sp_executesql @SQL,
N'@ID int,@Times int output',
@ID,
@Times output;
end
GO
--顶
create proc [dbo].[procDing]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
declare @tableName nvarchar(80);
set @tableName = dbo.funGetTableName(@SourceID,@key);
declare @SQL nvarchar(MAX);
set @SQL = N'update dbo.['+@tableName+N'] set Ding=Ding+1 where ID=@ID;select @Times=Ding from dbo.['+@tableName+N'] where ID=@ID';
exec sp_executesql @SQL,
N'@ID int,@Times int output',
@ID,
@Times output;
end
GOStatement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：SQL xp_cmdshellNext article：SQL xp_cmdshell