Problem with scripting objects through query

This article would help you to create a script that has multiple commands together. For example, suppose you want to drop a procedure and then create a new version. You usually have to separate these two statements, 'DROP PROCEDURE' and 'CREATE PROCEDURE', with the batch separator 'GO'. It is very simple to do this; we just need to concatenate these two batches.

I came across a very strange issue while doing such simple thing. Let's say I have a CREATE script for stored procedure named 'Testsp' defined as:

CREATE PROCEDURE Testsp
AS
BEGIN
 SELECT 1
END
GO

The DROP script is simple as:

DROP PROCEDURE Testsp
GO

I want to concatenate these two items dynamically. Let me show you the query i used to generate the script for concatenating the above two items, built dynamically. First I will build the drop procedure statement, and then add in the create procedure with a batch separator.

SELECT 'DROP PROCEDURE '+name + CHAR(13)+'GO'+CHAR(13)+ OBJECT_DEFINITION(object_id)+ CHAR(13)+'GO'+CHAR(13) 
FROM sys.objects 
WHERE name = 'Testsp'

By using the object_definition, it is easy to get the body of the stored procedure. I have used sys.objects to get the object_id, in case there are more stored procedures to be scripted.

I have used CHAR(13), which is a 'Carriage Return' character, in the script to concatenate the batch separator ' GO' with the other items. In other words, to write 'GO' on a new line, CHAR(13) is used.

The query scripts the stored procedure named 'Testsp' as:

When i tried to execute by copying from the Results window to Query window, i got a very strange error:

As shown in the figure above it shows the error as 'Incorrect syntax near 'GO'. This is because along with CHAR(13) , the batch separator 'GO' also requires the line feed character, i.e., CHAR(10) to be concatenated with it.

By rewriting the query again and concatenating CHAR(10) with CHAR(13), we will give the correct output.

SELECT 'DROP PROCEDURE '+name + CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+
       OBJECT_DEFINITION(object_id)+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10) 
FROM sys.objects
WHERE name = 'Testsp'

I again tried to copy the output of this query and executed it, and to my surprise this time it worked.

Coming to the conclusion, 'GO' is a batch separator and it requires both the Carriage Return and Line Feed codes to work well. I hope this article will be able to solve this scripting problem for others if they are creating multiple objects or multiple statements using dynamic code.