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, Jan 23 '13 at 05:58

mellamokb · Answer 1 · 2013-01-23T05:59:30.147

25

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 '13 at 05:59

answered Jan 23 '13 at 05:52

mellamokb

53,762
11
101
131

score 11 · Answer 2 · answered Mar 14 '13 at 09:34

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. — crokusek, Jun 24 '17 at 00:40
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. — crokusek, Jun 24 '17 at 03:46
@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. — MikeTeeVee, Jun 20 '19 at 11:52

score 5 · Answer 3 · edited May 23 '17 at 11:45

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.

score 3 · Answer 4 · answered Jan 23 '13 at 06:30

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 '13 at 06:30

Raj

10,165
2
41
50

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. – Husain Sanwerwala Jan 23 '13 at 06:38
1

+1 This is a awesome piece of information! I thought GO is just magic, but it's just SSMS specific feature... hah! – Mzn Jul 20 '14 at 05:37
1

@raj An example that requires go is multiple "Alter View ..." statements because each must be the first statement of a batch. – crokusek Jun 24 '17 at 00:29

score 1 · Answer 5 · answered Sep 25 '13 at 14:41

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.

Execute Dynamic Query with go in sql

5 Answers5

This is possible and very simple to do.

Linked

Related