Rudy Rodarte . com

Information

This article was written on 20 Sep 2010, and is filled under SQL Blogs.

Current post is tagged

, , ,

Incorrect syntax near ‘GO’ when creating Dynamic SQL

Update:

I created a quick video that shows why you cannot use ‘go’ inside sp_executesql.

Incorrect Syntax Near go in SQL Server from Rudy Rodarte on Vimeo.

From time to time, you will have to create some Dynamic SQL to create scripts that are used in SQL Reporting Services or something along those lines. However, when you want to add readability to the dynamic SQL scripts, you’ll have to add line breaks. Here is a simple example, which sets the database.


-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13)
SET @sql = @sql + 'GO' + CHAR(13)-- Print the command
PRINT (@sql)

 

Now, you’ll see the error below when you try to execute the script:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.

Something is awry, and it turns out that SQL Server is looking for a carriage return CHAR(13) and a line feed CHAR(10). The example below will execute successfully.

-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'GO' + CHAR(13) + CHAR(10)-- Print the command
PRINT (@sql)

 

With the Carriage Return and Line feed in the Dynamic SQL, your query should execute.
Source from MSDN SQL Server Forums.

UPDATE

Since I’ve had several questions over this topic, I want to make a clarification:

You cannot use GO in an  sp_executesql statement. Here is a quick sample:

DECLARE @BadStatment nvarchar(25)
SET @BadStatment =
N'print getdate();
go'

EXEC sp_executesql @BadStatment

As you well know, you'll see this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.

I was not using the code to feed sp_executesql so I did not test for that.

For you folks using  sp_executesql, I would say leave out the GO statements for now, if possible.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter