Combining multiple TSQL change scripts with skipping

Question

I have a bunch of TSQL change scripts, all named appropriately in sequence.

I want to combine these into one big script with a few twists. I include a version number function in the script that I update for each script so that once change 1 is run it returns 1, once 2 is run it returns 2 and so on. This function remains in the database and always returns the version of the schema/database.

I want to wrap each change script with a few lines that prevent a script that has already been run from running again, likewise it should prevent a change script from running on a schema version that is too "low". This allows me to bunch all the change scripts into one, and only missing changescripts will be applied when it all runs.

This is all fine, but I cant find a way to make osql / Query Analyzer / Sql Server Studio skip the parts that have alreay been run.

• GOTO won't work across batches (Scripts contain "GO")
• IF BEGIN END won't work likewise because of GO

Update: To reiterate, I don't need help remembering the current version number, I need a way to skip parts of the script to prevent already applied updates from reapplying.

I have tried a number of methods:

• I can wrap the batch in an db_executeSql statement or EXECUTE but this leads to scoping problems.
• I can wrap each batch in a IF dbo.DB_VERSION()!=REQUIRED_VERSION THEN BEGIN .... END construct, but this is messy and makes handling errors difficult.

Encountering situations where the change should not be applied is expected and is not an exceptional situation. So simply RETURNing when not applicable is not Ok.

Any other suggestions?

Answer 1

You can keep the version number if a table, and use the function to check the value in the table and compare in an IF statement to the version of the change you wish to make.