使用 go in sql 执行动态查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14473223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute Dynamic Query with go in sql
提问by Husain Sanwerwala
DECLARE @script VARCHAR(MAX);
SET @script =
'
create table ali(id decimal(10,0));
drop table ali;
go
create table ali(id decimal(10,0));
drop table ali;
'
EXEC (@script);
Error message occured when execute above query. Please tell me if you have an idea for resolve this.
执行上述查询时出现错误消息。请告诉我您是否有解决此问题的想法。
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'go'.
消息 102，级别 15，状态 1，第 4 行“go”附近的语法不正确。
Note : the above code for create and drop created table is just for example, i have some other dynamic queries with go statement. Please do not give this answer.
注意：上面创建和删除创建表的代码只是举例，我还有一些其他的带有 go 语句的动态查询。请不要给出这个答案。
DECLARE @script VARCHAR(MAX),
@script1 VARCHAR(MAX);
SET @script =
'
create table ali(id decimal(10,0));
drop table ali;
';
SET @script1 =
'
create table ali(id decimal(10,0));
drop table ali;
';
EXEC (@script);
EXEC (@script1);
回答by mellamokb
GOis actually not valid T-SQL:
GO实际上不是有效的 T-SQL：
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
GO 不是 Transact-SQL 语句；它是 sqlcmd 和 osql 实用程序以及 SQL Server Management Studio 代码编辑器识别的命令。
You will have to remove instances of GOin your dynamic SQL, or use one of the tools mentioned on the article (such as osqlfrom the command-line). Your query should still work with all instances of GOremoved from the dynamic SQL.
您必须删除GO动态 SQL中的 实例，或使用文章中提到的工具之一（例如osql从命令行）。您的查询应该仍然适用于GO从动态 SQL中删除的所有实例。
回答by MikeTeeVee
This is possible and very simple to do.
这是可能的，而且非常简单。
I think everyone here just got hung up on the "GO" keyword.
我想这里的每个人都被“GO”这个关键词挂了。
Solution:
解决方案：
--Your Script modified by adding a single line of code:
DECLARE @script nVarChar(MAX);--I changed from VarChar to nVarChar - you should always use nVarChar for Dynamic SQL.
SET @script =
'
create table ali(id decimal(10,0));
drop table ali;
go
create table ali(id decimal(10,0));
drop table ali;
'
--In case you have apostrophes in your script, you must escape them for the Exec() command. - 03/14/2013 - MCR.
SET @script = 'EXEC (''' + REPLACE(REPLACE(@script, '''', ''''''), 'GO', '''); EXEC(''') + ''');'--Just add this one line.
PRINT @script --See the command used (will be truncated in Select/Print, but not when Executing).
EXEC (@script);
For those looking for a solution where you dynamically concatenate multiple statements from a table:
对于那些正在寻找动态连接表中多个语句的解决方案的人：
--Example of compiling and chaining multiple DDL statments from data in a table:
-- DDL (Data Definition Language).
-- These are statements used to create, alter, or drop data structures.
-- They MUST be run in a single Batch.
-- The "GO" keyword is a SSMS syntax for splitting up batches - it is not an SQL keyword.
DECLARE @DDL_Statements TABLE
(
DDL nVarChar(MAX)
)
INSERT INTO @DDL_Statements (DDL)
SELECT 'create table ali(id decimal(10,0)); drop table ali;' UNION ALL
SELECT 'create table ali(id decimal(10,0)); drop table ali;'
DECLARE @SqlCommand nVarChar(MAX) = ''
SELECT @SqlCommand = @SqlCommand + 'EXEC(''' + REPLACE(DS.DDL, '''', '''''') + '''); '
FROM @DDL_Statements as DS --In case you have apostrophes in your script, you must escape them for the Exec() command. - 03/14/2013 - MCR.
PRINT @SqlCommand --See the command used (will be truncated in Select/Print, but not when Executing).
EXEC (@SqlCommand)
回答by Jacco
You simply can't use GOin a dynamic T-SQL query, as stated by @mellamokb.
GO正如@mellamokb所述，您根本无法在动态 T-SQL 查询中使用。
Since you don't want to run the separate SQL-queries, as stated in the second part of your question, you might get away by splitting the query in separate batches yourself.
由于您不想运行单独的 SQL 查询，如问题的第二部分所述，您可能会通过自己将查询分成不同的批次来逃脱。
You could split the query on GOusing a UDF. After splitting, execute the separated batches.
But it does not feel natural, to first create a string with GOin it and then break it apart a few moments later.
但是感觉并不自然，首先GO在其中创建一个字符串，然后在几分钟后将其分开。
回答by Raj
GO is a batch seperator and as pointed out by others is not valid SQL. As a matter of fact, you can go to SSMS - Tools - Options - Query Execution - SQL Server and enter some other text, for example COME to be used as your batch seperator.
GO 是一个批处理分隔符，正如其他人指出的那样，它不是有效的 SQL。事实上，您可以转到 SSMS - 工具 - 选项 - 查询执行 - SQL Server 并输入一些其他文本，例如 COME 用作您的批处理分隔符。
If you do that GO will not be recognized even in SSMS. You dynamic SQL should run even without the batch seperator. I do not see the need for GO here
如果你这样做，即使在 SSMS 中也不会识别 GO。即使没有批处理分隔符，您的动态 SQL 也应该运行。我认为这里不需要 GO
Raj
拉吉
回答by John Fraser
An example of when you would want to use GO in dynamic SQL would be I need to populate a few tables with data and want to use the GO n with n representing the amount of time I want it to run.
在动态 SQL 中使用 GO 的一个例子是，我需要用数据填充几个表，并希望使用 GO n，其中 n 表示我希望它运行的时间量。
DECLARE @statement TABLE
(
SqlCommand NVARCHAR(MAX)
)
DECLARE @sqlCommand NVARCHAR(MAX) = 'INSERT INTO [dbo].[tbl_table1] ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6],[Field7],[Field8],[Field9],[Field10],[Field11])SELECT ''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',RAND(),RAND(),RAND()
GO 10000'
INSERT INTO @statement
( SqlCommand )
VALUES ( @sqlCommand )
SET @sqlCommand = REPLACE(@sqlCommand, '[tbl_table1]', '[tbl_table2]')
INSERT INTO @statement
( SqlCommand )
VALUES ( @sqlCommand )
This code will work producing a table of select statements that I need to copy and run instead of being able to do
此代码将生成一个我需要复制和运行而不是能够执行的选择语句表
EXEC(@sqlCommand)
Which gives an incorrect syntax near '10000' error.
这给出了接近“10000”错误的错误语法。
