Execute Dynamic Query with go in sql

Question

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'.

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.

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);

If removing GO will work for you, then why u not removing it ? — Satinder singh
– Satinder singh, Commented Jan 23, 2013 at 5:58

mellamokb · Accepted Answer · 2013-01-23 05:59:30Z

29

GO is actually not valid 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.

You will have to remove instances of GO in your dynamic SQL, or use one of the tools mentioned on the article (such as osql from the command-line). Your query should still work with all instances of GO removed from the dynamic SQL.

edited Jan 23, 2013 at 5:59

answered Jan 23, 2013 at 5:52

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MikeTeeVee · Accepted Answer · 2013-03-14 09:34:24Z

This is possible and very simple to do.

I think everyone here just got hung up on the "GO" keyword.

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)

Suggest replace(@script, char(10)+'GO', char(10)+ ...). Still not perfect since could type "gotcha". Maybe Patindex() in a loop w/ trim() could ensure 'go' was alone on the line.
This works great except for cases where the statements depend on state between GO's. Example 1: A #temp table is created and then inserted into later on. temp table would have to be created in parent scope. Example 2: "Begin Transaction", Example 3: "Set ANSI_NULLS ON", etc.
@AhmedElbatt Good Point! You would need to update the 'GO' search string to include carriage-returns/new-lines wrapped around it. Then it should work to only replace GO when it is the only thing on the line. I think this may also be why SSMS requires GO to be on it's own separate line too. Still, this is just one more reason to use the 2nd Option I posted above; where you skip the "GO" Keyword search all together and use a Table to hold all the Statements you would like to run. Then you could capture errors that are thrown and log them against a ScriptID for debugging what/where it went wrong.

Community · Accepted Answer · 2017-05-23 11:45:40Z

6

You simply can't use GO in a dynamic T-SQL query, as stated by @mellamokb.

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.

You could split the query on GO using a UDF. After splitting, execute the separated batches.

But it does not feel natural, to first create a string with GO in it and then break it apart a few moments later.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 23, 2013 at 6:11

Jacco

3,2721 gold badge22 silver badges30 bronze badges

Comments

Raj · Accepted Answer · 2013-01-23 06:30:23Z

3

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.

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

Raj

answered Jan 23, 2013 at 6:30

Raj

10.9k2 gold badges47 silver badges53 bronze badges

4 Comments

Husain Sanwerwala Over a year ago

why you do not see the need of go here, the code i give here is just for example, but you believe me i have codes that required go keyword in dynamic.

Lzh Over a year ago

+1 This is a awesome piece of information! I thought GO is just magic, but it's just SSMS specific feature... hah!

crokusek Over a year ago

@raj An example that requires go is multiple "Alter View ..." statements because each must be the first statement of a batch.

Ben Over a year ago

GO is needed to continue execution in case of error

John Fraser · Accepted Answer · 2013-09-25 14:41:41Z

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.

        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.

I know, this is a very old answer, but instead of GO 10000 you could simply write a WHILE loop (WHILE @i < 10000 BEGIN SET @i +=1; INSERT ...; END)

Collectives™ on Stack Overflow

Execute Dynamic Query with go in sql

5 Answers 5

Comments

This is possible and very simple to do.

4 Comments

Comments

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

This is possible and very simple to do.

4 Comments

Comments

4 Comments

1 Comment

Linked

Related