SQL with Manoj

–> Question:

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on…….it enables me to execute all the code in one go.

I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it’s running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

I’ve used a semicolon at the end of each statement in the sp?
 

–> My Answer:

First of all “GO” is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here.

Secondly “GO” is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP. More on MSDN about GO, link.

Now, why do you want to use GO within an SP?

If the SP is large, then try creating multiple SP and execute them in sequence.
 

–> Another Answer by Erland:

You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application – and SQLCMD is a client.

It’s a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

1) Separate batches. (Don’t forget to check @@trancount in the beginning of each batch.)=
2) But most commands in EXEC().
3) Have a program (in C# or whatever) to run the whole thing.

My preference is for the latter.
 

Ref Link and this.