No "GO" allowed in Execute

Question

No "GO" allowed in Execute

digitalox

SSCommitted

Points: 1830
More actions
May 21, 2014 at 1:57 pm

#289143

Hello,
I am trying to execute some SQL using the EXECUTE command but am getting the following error
Incorrect syntax near 'GO'.
e.g.
EXEC ('
use master
GO
select 1
'
)
Is there some way to execute SQL with GO statements in it from the context of a stored procedure?
Thank You
Scott

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

No. You won't be able to do that and use the GO command.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

To amplify Jason's post above, the GO "command" isn't actually a command. It's a batch separator for SSMS and is not a part of T-SQL, which is why you can't run it in dynamic SQL. For the code you posted, just remove it. The USE statement will work just fine.

To prove that it works, please run the following code...

EXEC ('

use master;

select 1, DB_NAME()

'

);

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

digitalox SSCommitted Points: 1830 More actions · Answer 3

Thanks. I know that the GO can be removed in the example. The actual code I have will need the GO statement because it is SQL creating objects and such, so that does not resolve the issue. I guess executing externally via SQLCMD or such is the only alternative here?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

digitalox (5/21/2014)
Thanks. I know that the GO can be removed in the example. The actual code I have will need the GO statement because it is SQL creating objects and such, so that does not resolve the issue. I guess executing externally via SQLCMD or such is the only alternative here?

Or execute multiple steps. The first one to create the objects. Then run a second dynamic query to reference those objects.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

It won't be just "one" to create the objects. If the objects are procs, views, functions, and the like, you'll need one EXEC for each object.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)