USE [YXComments]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[procAddComment]
        @ParentID = 0,
        @SourceID = 1,
        @NickName = N'afasf',
        @Content = N'sdfasdfsdf',
        @IP = N'127.0.0.1',
        @City = N'南阳',
        @BeFiltered = 0,
        @Enable = 1,
        @Key = N'soft'

SELECT    'Return Value' = @return_value

/****** Script for SelectTopNRows command from SSMS  ******/

ALTER proc [dbo].[procAddComment]
(
@ParentID int,
@SourceID int,
@NickName nvarchar(20),
@Content nvarchar(300),
@IP nvarchar(30),
@City nvarchar(30),
@BeFiltered bit,
@Enable bit,
@Key nvarchar(50)
)
as
begin
    declare @tableName nvarchar(80);
    declare @tableArea int;
    declare @mod int;
    
    declare @Size int;
    set @Size = 100000;
    
    set @mod = @SourceID % @Size;
    if @mod > 0 
        set @tableArea = Cast(@SourceID / @Size as int) + 1;        
    else    
        set @tableArea = Cast(@SourceID / @Size as int);
    
    set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
    
    if not Exists(select * from [CommentsTables] where [Key]=@Key and [TableName]=@tableName)    
    begin
        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,
        [Enable] [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);
        
        
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_ParentID]  DEFAULT ((0)) FOR [ParentID]
        GO
        ');
        
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Datetime]  DEFAULT (getdate()) FOR [Datetime]
        GO
        ');
        
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_BeFiltered]  DEFAULT ((0)) FOR [BeFiltered]
        GO
        ');        

        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Enable]  DEFAULT ((0)) FOR [Enable]
        GO
        ');
    
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Lou]  DEFAULT ((1)) FOR [Lou]
        GO
        ');
            
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Ding]  DEFAULT ((0)) FOR [Ding]
        GO
        ');
        
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Cai]  DEFAULT ((0)) FOR [Cai]
        GO
        ');
        
        Insert Into [CommentsTables]([Key],[TableName]) values(@Key,@tableName);
    end 
    
    
    set @NickName = Replace(@NickName,'''','''''');
    set @Content = Replace(@Content,'''','''''');
    set @IP = Replace(@IP,'''','''''');
    set @City = Replace(@City,'''','''''');
    
    Exec('Insert Into dbo.['+@tableName+'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Enable])
    values ('+@ParentID+','+@SourceID+','''+@NickName+''','''+@Content+''','''+@IP+''','''+@City+''','+@BeFiltered+','+@Enable+');')

end 







GO