Dynamic query and incorrect syntax near 'GO' error

Introduction

Sometimes we are experiencing a common Microsoft SQL Server error: Incorrect syntax near 'GO'. What is this? And in which scenario do we get this error? One most common scenario is when we write a dynamic query. Here I am trying to discuss some basic concepts about dynamic queries.

What is dynamic query?

1. Dynamic query is a query which is evaluated at runtime.
2. You have to store dynamic query in a variable as a string.
3. You have to execute the dynamic query by either EXEC command or sp_executesql statement.
4. You cannot use 'GO' statement inside a dynamic query. If you try you will get "Incorrect syntax near 'GO'" error.

What does 'GO' statement do?

1. 'GO' statement forces SQL Server Management Studio for execute code batch or code group immediately.
2. 'GO' signals the end of batch or code group.
3. Each code batch separated by 'GO' is compiled like one execution plan.
4. Any error in one batch will not affect the next batch.

For example: Run the following two lines first. you will see as an exception is occurred for first statement, the next statement will not execute. 

exec('select convert(int,''b'')')
exec ('select getdate()')  

Now run the following two lines. Go to the result pane. Though the first statement has thrown an exception, the second statement has executed as the second statement is in different batch.

exec('select convert(int,''b'')')
go
exec ('select getdate()')
go

5. Any local variable used in one batch is not accessible to another batch.

Try the following example:- 

DECLARE @MyMsg VARCHAR(50) 
SELECT @MyMsg = 'Hello, World.' 
GO -- @MyMsg is not valid after this GO ends the batch. 
-- Yields an error because @MyMsg not declared in this batch. 
PRINT @MyMsg 
GO  

Dynamic query does not support 'GO'  

Dynamic SQL executer does not support multiple batch. that's mean dynamic SQL query treated as a single batch. You cannot differentiate query into different different batch by 'GO' statement.

Another reason for not to support 'GO' statement by SQL parser of dynamic query is that, 'GO' is not a TSQL statement so funny right? It only recognized by SQLCMD and OSQL utilities and SQL Server Management Code Editor. I do not know how many other statements we are using that are not actually TSQL...

What is the main problem of dynamic SQL?

The main problem is you cannot guess any syntax error of a dynamic query when verify your entire query. I use Print command instead of Exec command first. Then execute. In result pane my desired query is shown. I copy the query and execute in another query analyzer to verify. 

When should you use a dynamic query?

When your query is not static. That's mean when any part of you query will be evaluated at runtime or when your query parameter is not fixed value. Parameter value will be changed at runtime.

Let's consider a simple example.

you want to do a task for each user created database in your server. think 1 minute. try to write a solution before seeing my solution below.

The pseudo-code for the solution is:

• Step 1: loop each user created database
• Step 2: check is the database is in online
• Step 3: if database is in online
• Step 4: do specific task for that database
• Step 5: end loop

The solution code is as below

DECLARE @EndLine nchar(2); SET @EndLine = NCHAR(13) + NCHAR(10);
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

DECLARE MY_CURSOR Cursor --traverse all user created database

FOR 
SELECT NAME FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') AND state = 0 
--here state = 0 means online
--'master', 'tempdb', 'model', 'msdb' - these 4 tables are called system table
--all database schema are stored in these table and they are also responsible
--for some other specific task like temorary tables used in sql query
--are stored in 'tempdb' database

Open My_Cursor

Fetch NEXT FROM MY_Cursor INTO @dbName 
While (@@FETCH_STATUS = 0)
BEGIN

    -------------->>>>>>>>>>
    --Create a table to each database
    --Task#1: at first check is the table is exist
    SET @sql =
        N'IF OBJECT_ID(''''dbo.A_TmpTable'''', ''''U'''') is not null' + @EndLine +
        N'    DROP TABLE dbo.A_TmpTable'
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';    
    EXEC (@BigSQL)

    SET @sql = N' Create Table A_TmpTable' + @EndLine
    SET @sql += N'(' + @EndLine
    SET @sql += N'[DB_Name] Varchar(100),' + @EndLine
    SET @sql += N'[Current_TIme] datetime' + @EndLine
    SET @sql += N')'
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';
    EXEC (@BigSQL)

    --Then insert some data to the table.
    SET @sql=N' Insert Into A_TmpTable([DB_Name], [Current_TIme]) 
            values( DB_Name(), getdate() );'
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';
    EXEC (@BigSQL)
    --------------<<<<<<<<<<


    -------------->>>>>>>>>>
    --Create a stored procedure
    --The task of procedure is to display table data that is DB_Name and Current_Time
    --at first check is the procedute is exist
    SET @sql =
        N'IF OBJECT_ID(''''dbo.A_TmpProcedure'''') is not null' + @EndLine +
        N'    DROP PROCEDURE dbo.A_TmpProcedure'
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';    
    EXEC (@BigSQL)

    --Then create the procedure
    SET @sql =
        N'CREATE PROCEDURE dbo.A_TmpProcedure' + @EndLine +
        N'AS' + @EndLine +
        N'BEGIN' + @EndLine +
        N' Select * From A_TmpTable;' + @EndLine +
        N'END'
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';    
    EXEC (@BigSQL)
    
    --finally run the stored procedute    
    SET @BigSQL = 'USE [' + @dbName + ']; EXEC dbo.A_TmpProcedure';    
    EXEC (@BigSQL)
    --------------<<<<<<<<<<


    FETCH NEXT FROM MY_CURSOR INTO @dbName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO    

In the above code the main focused thing is database name. As I do not want to write hard-coded query for each database, I use cursor. Cursor in MSSQL database is act as loop. loop on table record. I pick all currently online database names from the sys.database table and loop through on each database.

Now for each database see my first task. I am checking whether the specific table is exist in currently fetched database or not. Observe very carefully that here the database name is a variable which is fetching by cursor. 

Then I create table, then insert a row in that table, then create a stored procedure that will show the data of newly created table. And all these things are doing for one by one database that is not static.  

Point of interest

As a database developer sometimes we need to write dynamic query like change status or change recovery model or backup transaction log of databases. Also sometimes need to write dynamic query in stored procedure in which the value of variable in "where" clause may not static. I have tried to clear some basic things about dynamic query here.

Here some terms are used but not discussed briefly as they are beyond of my topic. like SQLCMD, OSQL, CURSOR. Some concepts like table construction, procedure construction  are not also discussed here.

In the end

Today morning my boss asked me for writing a dynamic query that was a very cool staff. During writing that query I have straggled much and think to share my knowledge with you. Thank you very much. Any suggestion is highly appreciated from you.