首页 新闻 搜索 专区 学院

SQLServer 动态创建表,无法加索引默认值等,怎么搞??

0
[已关闭问题] 关闭于 2012-04-25 17:31

执行了这个存储过程之后出现了一堆错误,表创建成功了,但是索引什么的都没加上,这是怎么回事??

 

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

 

消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。
消息 102,级别 15,状态 1,第 2 行
'GO' 附近有语法错误。

(1 行受影响)
消息 515,级别 16,状态 2,第 1 行
不能将值 NULL 插入列 'Cai',表 'YXComments.dbo.comments_soft1';列不允许有 Null 值。INSERT 失败。
语句已终止。

(1 行受影响)

 

 

 

/****** 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
fun5的主页 fun5 | 初学一级 | 园豆:4
提问于:2012-04-25 17:19
< >
分享
所有回答(1)
0
USE [YXComments]
GO

DECLARE @return_value int

EXEC [dbo].[procAddComment] 0,1,N'afasf',N'sdfasdfsdf',N'127.0.0.1',N'南阳',0,1,N'soft' OUTPUT @return_value

PRINT @return_value
johLife | 园豆:232 (菜鸟二级) | 2012-04-25 17:38
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册