SQLServer 动态创建表,无法加索引默认值等,怎么搞??
Jun 07, 2016 pm 03:39 PM执行了这个存储过程之后出现了一堆错误,表创建成功了,但是索引什么的都没加上,这是怎么回事?? 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>
消息 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>

Artikel Panas

Alat panas Tag

Artikel Panas

Tag artikel panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

Bagaimana untuk menyelesaikan masalah yang objek bernama sudah wujud dalam pangkalan data sqlserver

Bagaimana untuk mengimport fail mdf ke dalam sqlserver

Apa yang perlu dilakukan jika perkhidmatan sqlserver tidak dapat dimulakan

Tukar cakera tetap VirtualBox kepada cakera dinamik dan sebaliknya

Bagaimana untuk memulihkan pangkalan data yang dipadam secara tidak sengaja dalam sqlserver

Bagaimana untuk menyemak nombor port sqlserver

Bagaimana untuk memadam sqlserver jika pemasangan gagal?
