running conditional DDL statements on sql server

Question

i have a situation where i want to check a certain column ( like version number) and then apply a bunch of ddl changes

trouble is i am not able to do it with in a IF BEGIN END block, since DDL statements require a GO separator between them, and TSQL wont allow that.

I am wondering if there is any way around to accomplish this

Answer 1

You don't need to use a full block. A conditional will execute the next statement in its entirety if you don't use a BEGIN/END -- including a single DDL statement. This is equivalent to the behavior of if in Pascal, C, etc. Of course, that means that you will have to re-check your condition over and over and over. It also means that using variables to control the script's behavior is pretty much out of the question.

[Edit: CREATE PROCEDURE doesn't work in the example below, so I changed it to something else and moved CREATE PROCEDURE for a more extended discussion below]

If ((SELECT Version FROM table WHERE... ) <= 15)
CREATE TABLE dbo.MNP (
....
)
GO

If ((SELECT Version FROM table WHERE... ) <= 15)
ALTER TABLE dbo.T1
ALTER COLUMN Field1 AS CHAR(15)
GO

...

Or something like that, depending on what your condition is.

Unfortunately, CREATE/ALTER PROCEDURE and CREATE/ALTER VIEW have special requirements that make it much harder to work with. They are pretty much required to be the only thing in a statement, so you can't combine them with IF at all.

For many scenarios, when you want to "upgrade" your objects, you can work it as a conditional drop followed by a create:

IF(EXISTS(SELECT * FROM sys.objects WHERE type='p' AND object_id = OBJECT_ID('dbo.abc')))
DROP PROCEDURE dbo.abc
GO

CREATE PROCEDURE dbo.abc
AS
    ...
GO

If you do really need conditional logic to decide what to do, then the only way I know of is to use EXECUTE to run the DDL statements as a string.

If ((SELECT Version FROM table WHERE... ) <= 15)
EXECUTE 'CREATE PROC dbo.abc 
AS
    ....
')

But this is very painful. You have to escape any quotes in the body of the procedure and it's really hard to read.

Depending on the changes that you need to apply, you can see all this can get very ugly fast. The above doesn't even include error checking, which is a royal pain all on its own. This is why hordes of toolmakers make a living by figuring out ways to automate the creation of deployment scripts.

Sorry; there is no easy "right" way that works for everything. This is just something that TSQL supports very poorly. Still, the above should be a good start.

Answer 2

GO is recognised by client tools, not by the server. You can have CREATEs in your stored procedures or ad-hoc queries with no GO's.

Answer 3

Multiple "IF" statements? You can test then for the success of subsequent DDL statements

Dynamic SQL? EXEC ('ALTER TABLE foo WITH CHECK ADD CONSTRAINT ...')?

As mentioned, GO is a client only batch separator to break down a single SQL text block into batches that are submitted to the SQL Server.