Databases
StackOverflow2013
Postsdbo
POCombining multiple TSQL change scripts with skipping
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombining multiple TSQL change scripts with skipping
    text
    copied!<p>I have a bunch of TSQL change scripts, all named appropriately in sequence.</p> <p>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.</p> <p>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.</p> <p>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.</p> <ul> <li>GOTO won't work across batches (Scripts contain "GO")</li> <li>IF BEGIN END won't work likewise because of GO</li> </ul> <p><strong>Update:</strong> 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.</p> <p>I have tried a number of methods:</p> <ul> <li>I can wrap the batch in an <code>db_executeSql</code> statement or <code>EXECUTE</code> but this leads to scoping problems.</li> <li>I can wrap each batch in a IF <code>dbo.DB_VERSION()!=REQUIRED_VERSION THEN BEGIN .... END</code> construct, but this is messy and makes handling errors difficult.</li> </ul> <p>Encountering situations where the change should not be applied is expected and is not an exceptional situation. So simply <code>RETURN</code>ing when not applicable is not Ok.</p> <p>Any other suggestions?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload