Archive
Using GO statement within a Stored Procedure – MSDN TSQL forum
–> 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.
DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum
–> Question:
Running SQL Server 2008 R2. We currently apply schema and data changes to databases (for software upgrades) using separate scripts. Sometimes, based on the changes required this results in several scripts for a single upgrade. I’d like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues. Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).
I’m aware of the fact that we can strip out these batch separators, but what I’m not clear on is what, if any, negative impact that would have. Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?
Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first). If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN…END or something similar so the column existence check would only have to be performed once for that group of changes?
–> My Answer:
You can remove GO or batch separator if you don’t have:
1. same variables declared in more than 1 batch
2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE’ must be the first statement in a query batch.)
3. there could be more things, just check based upon my above 2 points.
Ref Link.
GO is not a valid TSQL statement
SQL Server support 2 commands that are not Transact SQL Statements, but are recognized by SQL Server utilities such as: SQL Server Management Studio (SSMS), sqlcmd and osql.
These are:
1. GO (Batch separator)
2. \ (Backslash)
The above 2 keywords can be used to facilitate the readability and execution of batches and scripts.
–> “GO” (batch separator):
– “GO” is not a valid Transact-SQL statement.
– SQL Server utilities interpret “GO” as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.
– The “GO” command is never send to the server.
– Scope of Variables is limited to its batch, once defined in a batch they cannot be used after the “GO” command in another batch.
– You can also use the “GO” command to execute same batch multiple times. Just add a number after it, like: “GO 100” will execute the batch 100 times.
– Command “GO” can even be changed with some other name. In SSMS goto menu, select Tools -> Options -> Query Execution: Here you change the batch separator by-default “GO” to something else. But the SQL Scripts you’ve created earlier will fail if they contain “GO” keyword now.
–> \ (Backslash): You can break a long string into multiple lines for good readability.
sqlcmd & osql are command line utilities so the parameters passed to them and options they take should not be split into multiple lines. Otherwise it may raise an error or you may get unexpected results.
To maintain formatting and readability of your SQL statements you can use “\” (backslash) in your SQL statements when putting them as parameters in sqlcmd or osql utilities.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 