1 ALTER proc [dbo].[procAddComment]
  2 (
  3 @ParentID int,
  4 @SourceID int,
  5 @NickName nvarchar(20),
  6 @Content nvarchar(300),
  7 @IP nvarchar(30),
  8 @City nvarchar(30),
  9 @BeFiltered bit,
 10 @Disable bit,
 11 @Key nvarchar(50),
 12 @InsertedID int OUTPUT
 13 )
 14 as
 15 begin
 16     declare @tableName nvarchar(80);
 17     declare @tableArea int;
 18     declare @mod int;
 19     
 20     declare @Size int;
 21     set @Size = 1000;
 22     
 23     set @mod = @SourceID % @Size;
 24     if @mod > 0 
 25         set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;        
 26     else    
 27         set @tableArea = Cast((@SourceID-1) / @Size as int);
 28         
 29 
 30 
 31     
 32     set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
 33     
 34     if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName)    
 35     begin
 36     
 37         declare @StartID int;
 38         declare @EndID int;
 39         
 40         set @EndID = @tableArea * @Size;
 41         set @StartID = @EndID - (@Size-1);
 42     
 43     
 44         --创建表
 45         declare @CreateSQL nvarchar(MAX);
 46         set @CreateSQL = 
 47         'Create table [dbo].['+@tableName+'](
 48         [ID] [int] IDENTITY(1,1) NOT NULL,
 49         [ParentID] [int] NOT NULL,
 50         [SourceID] [int] NOT NULL,
 51         [NickName] [nvarchar](20) NOT NULL,
 52         [Content] [nvarchar](300) NOT NULL,
 53         [Datetime] [datetime] NOT NULL,
 54         [IP] [nvarchar](30) NOT NULL,
 55         [City] [nvarchar](30) NOT NULL,
 56         [BeFiltered] [bit] NOT NULL,
 57         [Disable] [bit] NOT NULL,
 58         [Lou] [int] NOT NULL,
 59         [Ding] [int] NOT NULL,
 60         [Cai] [int] NOT NULL,
 61          CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED 
 62         (
 63             [ID] ASC
 64         )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 65         ) ON [PRIMARY]'
 66         
 67         EXEC(@CreateSQL);
 68         
 69         
 70         --创建索引 ID DESC
 71         EXEC('        
 72         CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+'] 
 73         (
 74             [ID] DESC
 75         )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]');
 76         
 77         --创建索引 Ding DESC
 78         EXEC('
 79         CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+'] 
 80         (
 81             [Ding] DESC
 82         )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]');
 83         
 84         --创建索引 SourceID DESC
 85         EXEC('
 86         CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+'] 
 87         (
 88             [SourceID] DESC
 89         )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]');
 90 
 91         
 92         --创建默认值
 93         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_ParentID]  DEFAULT ((0)) FOR [ParentID]');
 94         
 95         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Datetime]  DEFAULT (getdate()) FOR [Datetime]');
 96         
 97         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_BeFiltered]  DEFAULT ((0)) FOR [BeFiltered]');    
 98 
 99         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Disable]  DEFAULT ((0)) FOR [Disable]');
100     
101         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Lou]  DEFAULT ((1)) FOR [Lou]');
102             
103         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Ding]  DEFAULT ((0)) FOR [Ding]');
104         
105         EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Cai]  DEFAULT ((0)) FOR [Cai]');
106         
107         
108         --
109         Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
110     end 
111     
112     
113     set @NickName = Replace(@NickName,'''','''''');
114     set @Content = Replace(@Content,'''','''''');
115     set @IP = Replace(@IP,'''','''''');
116     set @City = Replace(@City,'''','''''');
117     
118     Exec('Insert Into dbo.['+@tableName+'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable])
119     values ('+@ParentID+','+@SourceID+','''+@NickName+''','''+@Content+''','''+@IP+''','''+@City+''','+@BeFiltered+','+@Disable+');');
120     
121     set @InsertedID = SCOPE_IDENTITY();--这一句有问题
122     
123 end