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<=@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
go