Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > SQLServer 动态创建表，无法加索引默认值等，怎么搞？？

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

WBOY
Release： 2016-06-07 15:39:39
Original
1130 people have browsed it

执行了这个存储过程之后出现了一堆错误，表创建成功了，但是索引什么的都没加上，这是怎么回事？？ USE [ YXComments ] GO DECLARE @return_value int EXEC @return_value = [ dbo ] . [ procAddComment ] @ParentID = 0 , @SourceID = 1 , @NickName = N '

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

 

<span>USE</span> <span>[</span><span>YXComments</span><span>]</span>
<span>GO</span>

<span>DECLARE</span>    <span>@return_value</span> <span>int</span>

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

<span>SELECT</span>    <span>'</span><span>Return Value</span><span>'</span> <span>=</span> <span>@return_value</span>
Copy after login

 

消息 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 行受影响)

 

 

 

<span>/*</span><span>***** Script for SelectTopNRows command from SSMS  *****</span><span>*/</span>

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

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

</span><span>end</span> 







<span>GO</span>
Copy after login
Related labels：
sqlserver create dynamic how index default value
source：php.cn
Previous article：sqlserver 随机数问题 Next article：编译完mysql后实现自启动
Statement of this Website
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
Latest Articles by Author
Latest Issues
SQL Server: Using CASE WHEN expression with WHERE clause I have a new SpringBoot project with SQLServer and I need to replace the MySQL native quer...
From 2024-04-03 14:46:55
0
1
351
Unable to connect to SQL Server in Laravel I can't connect to sql-server in Laravel. I have given sqlserver configuration values ​​in...
From 2024-03-31 13:08:48
0
1
395
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template