Why SQL Server does not drop the procedure and recreate the object when the code is written properly?

This is bit funny, it took few minutes to figure out the reason for getting “There is already an object named '' in the database.” error when the code for dropping and recreating is properly written. It is one of key things to remember when coding, hence sharing the experience.

Have a look at the code and the error thrown. Note that it is not the actual code, the code below is written to show the scenario. Please assume that there are many other statements before and after the code given.

1.  
2. ...
3. ...
4.  
5. GO
6.  
7. -- dropping GetSalesFor2007 if exist
8. -- and creating
9. IF OBJECT_ID('dbo.GetSalesFor2007') IS NOT NULL
10.     DROP PROC dbo.GetSalesFor2007
11. GO
12. CREATE PROCEDURE dbo.GetSalesFor2007
13. AS
14. BEGIN
15.     
16.     SELECT SalesOrderID, SalesOrderNumber
17.     FROM Sales.SalesOrderHeader
18.     WHERE OrderDate BETWEEN '01/01/2007'
19.         AND '12/31/2007 23:59:59.000'
20. END
21.  
22. -- dropping GetSalesFor2008 if exist
23. -- and creating
24. IF OBJECT_ID('dbo.GetSalesFor2008') IS NOT NULL
25.     DROP PROC dbo.GetSalesFor2008
26. GO
27. CREATE PROCEDURE dbo.GetSalesFor2008
28. AS
29. BEGIN
30.     
31.     SELECT SalesOrderID, SalesOrderNumber
32.     FROM Sales.SalesOrderHeader
33.     WHERE OrderDate BETWEEN '01/01/2008'
34.         AND '12/31/2008 23:59:59.000'
35. END
36.  
37. ...
38. ...

As you see, SQL Server tries to create the procedure (Line no: 27) without dropping it using the DROP PROC statement given (Line no: 25). Simply, the reason for this is, missing the batch separator between end of first procedure and DROP statement of second procedure.

What is a batch?
T-SQL Batch is a collections of SQL statements that need to be parsed, normalized and executed as a single unit. The end of the batch is indicated using GO statement (Read more on GO: http://dinesql.blogspot.com/2009/01/separating-batches-using-new-word.html). There are two important points we need to remember when working with T-SQL batches;

1. The boundaries for scopes of variables are determined using batch separators, hence variables declared in one batch cannot be used beyond the GO statement. In other words, variables declared cannot be used in a different batches other than the batch it is declared.
2. Most DDL statements require separate batches and may not be combined with other statements.

If the second point is clearly understood, then the reason for above error can be figured out. Statements like CREATE PROC, CREATE VIEW cannot be combined with other statements in the batch and these statements must start with a new batch. If you note the DROP statement for the second procedure (Line no: 25), you will see that there is no GO statement in between end of first procedure and beginning of DROP statement for the second procedure. Therefore the DROP statement becomes a part of the first batch which contains the first procedure, making the DROP statement as part of first procedure. Have a look on below output;

As you see, the DROP statement is in the body of first procedure. If we place a GO statement at the end of the first procedure, it will not become a part of first procedure.

Here are all statements that require a separate batch; CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW.