当前位置：Gxlcms > mysql > SQLServer动态创建表，无法加索引默认值等，怎么搞？？

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

时间：2021-07-01 10:21:17 帮助过：41人阅读

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

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

  1. <span>USE</span> <span>[</span><span>YXComments</span><span>]</span>
  2. <span>GO</span>
  3. <span>DECLARE</span>    <span>@return_value</span> <span>int</span>
  4. <span>EXEC</span>    <span>@return_value</span> <span>=</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>procAddComment</span><span>]</span>
  5.         <span>@ParentID</span> <span>=</span> <span>0</span><span>,
  6.         </span><span>@SourceID</span> <span>=</span> <span>1</span><span>,
  7.         </span><span>@NickName</span> <span>=</span> N<span>'</span><span>afasf</span><span>'</span><span>,
  8.         </span><span>@Content</span> <span>=</span> N<span>'</span><span>sdfasdfsdf</span><span>'</span><span>,
  9.         </span><span>@IP</span> <span>=</span> N<span>'</span><span>127.0.0.1</span><span>'</span><span>,
  10.         </span><span>@City</span> <span>=</span> N<span>'</span><span>南阳</span><span>'</span><span>,
  11.         </span><span>@BeFiltered</span> <span>=</span> <span>0</span><span>,
  12.         </span><span>@Enable</span> <span>=</span> <span>1</span><span>,
  13.         </span><span>@Key</span> <span>=</span> N<span>'</span><span>soft</span><span>'</span>
  14. <span>SELECT</span>    <span>'</span><span>Return Value</span><span>'</span> <span>=</span> <span>@return_value</span>

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

  1. <span>/*</span><span>***** Script for SelectTopNRows command from SSMS  *****</span><span>*/</span>
  2. <span>ALTER</span> <span>proc</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>procAddComment</span><span>]</span><span>
  3. (
  4. </span><span>@ParentID</span> <span>int</span><span>,
  5. </span><span>@SourceID</span> <span>int</span><span>,
  6. </span><span>@NickName</span> <span>nvarchar</span>(<span>20</span><span>),
  7. </span><span>@Content</span> <span>nvarchar</span>(<span>300</span><span>),
  8. </span><span>@IP</span> <span>nvarchar</span>(<span>30</span><span>),
  9. </span><span>@City</span> <span>nvarchar</span>(<span>30</span><span>),
  10. </span><span>@BeFiltered</span> <span>bit</span><span>,
  11. </span><span>@Enable</span> <span>bit</span><span>,
  12. </span><span>@Key</span> <span>nvarchar</span>(<span>50</span><span>)
  13. )
  14. </span><span>as</span>
  15. <span>begin</span>
  16.     <span>declare</span> <span>@tableName</span> <span>nvarchar</span>(<span>80</span><span>);
  17.     </span><span>declare</span> <span>@tableArea</span> <span>int</span><span>;
  18.     </span><span>declare</span> <span>@mod</span> <span>int</span><span>;
  19.     
  20.     </span><span>declare</span> <span>@Size</span> <span>int</span><span>;
  21.     </span><span>set</span> <span>@Size</span> <span>=</span> <span>100000</span><span>;
  22.     
  23.     </span><span>set</span> <span>@mod</span> <span>=</span> <span>@SourceID</span> <span>%</span> <span>@Size</span><span>;
  24.     </span><span>if</span> <span>@mod</span> <span>></span> <span>0</span> 
  25.         <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>;        
  26.     </span><span>else</span>    
  27.         <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>);
  28.     
  29.     </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>));
  30.     
  31.     </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>)    
  32.     </span><span>begin</span>
  33.         <span>declare</span> <span>@CreateSQL</span> <span>nvarchar</span>(<span>MAX</span><span>);
  34.         </span><span>set</span> <span>@CreateSQL</span> <span>=</span> 
  35.         <span>'</span><span>Create table [dbo].[</span><span>'</span><span>+</span><span>@tableName</span><span>+</span><span>'</span><span>](
  36.         [ID] [int] IDENTITY(1,1) NOT NULL,
  37.         [ParentID] [int] NOT NULL,
  38.         [SourceID] [int] NOT NULL,
  39.         [NickName] [nvarchar](20) NOT NULL,
  40.         [Content] [nvarchar](300) NOT NULL,
  41.         [Datetime] [datetime] NOT NULL,
  42.         [IP] [nvarchar](30) NOT NULL,
  43.         [City] [nvarchar](30) NOT NULL,
  44.         [BeFiltered] [bit] NOT NULL,
  45.         [Enable] [bit] NOT NULL,
  46.         [Lou] [int] NOT NULL,
  47.         [Ding] [int] NOT NULL,
  48.         [Cai] [int] NOT NULL,
  49.          CONSTRAINT [PK_</span><span>'</span><span>+</span><span>@tableName</span><span>+</span><span>'</span><span>] PRIMARY KEY CLUSTERED 
  50.         (
  51.             [ID] ASC
  52.         )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  53.         ) ON [PRIMARY]</span><span>'</span>
  54.         
  55.         <span>EXEC</span>(<span>@CreateSQL</span><span>);
  56.         
  57.         
  58.         </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]
  59.         GO
  60.         </span><span>'</span><span>);
  61.         
  62.         </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]
  63.         GO
  64.         </span><span>'</span><span>);
  65.         
  66.         </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]
  67.         GO
  68.         </span><span>'</span><span>);        
  69.         </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]
  70.         GO
  71.         </span><span>'</span><span>);
  72.     
  73.         </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]
  74.         GO
  75.         </span><span>'</span><span>);
  76.             
  77.         </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]
  78.         GO
  79.         </span><span>'</span><span>);
  80.         
  81.         </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]
  82.         GO
  83.         </span><span>'</span><span>);
  84.         
  85.         </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>);
  86.     </span><span>end</span> 
  87.     
  88.     
  89.     <span>set</span> <span>@NickName</span> <span>=</span> <span>Replace</span>(<span>@NickName</span>,<span>''''</span>,<span>''''''</span><span>);
  90.     </span><span>set</span> <span>@Content</span> <span>=</span> <span>Replace</span>(<span>@Content</span>,<span>''''</span>,<span>''''''</span><span>);
  91.     </span><span>set</span> <span>@IP</span> <span>=</span> <span>Replace</span>(<span>@IP</span>,<span>''''</span>,<span>''''''</span><span>);
  92.     </span><span>set</span> <span>@City</span> <span>=</span> <span>Replace</span>(<span>@City</span>,<span>''''</span>,<span>''''''</span><span>);
  93.     
  94.     </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])
  95.     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>)
  96. </span><span>end</span> 
  97. <span>GO</span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！