SQL script versioning

[ResonantWorks]

Hi.

Does the latest InstallAware support sql script versioning as in InstallShield 2008?

To quote InstallShield's docs:

Specifying a Version Number for a SQL Script File
"...The installation checks the current schema version that is on the target database. The schema version is stored in the ISSchema column of the custom table named InstallShield. When you specify a schema version for a SQL script, the installation runs the script only if the script schema version number is greater than the current schema version number. Once the script is executed, the installation updates the current schema version on the target database to reflect the new schema version number"

[CandiceJones]

Sure, this is just a few extra lines of SQL code that you can type into your SQL script.

[ResonantWorks]

Can you give a simple example of how this would be done?
The only code I can think of right now is:

IF( SELECT CurrentVersion FROM VersionControl >= [UPDATE_VERSION] ) GOTO Finished
CREATE TABLE...
GO
INSERT ...
GO
INSERT ...
GO
UPDATE VersionControl SET CurrentVersion = [UPDATE_VERSION] )
GO
Finished:

But this would not work due to the GOs terminating the batch, causing the label "Finished" to be hidden from the rest.

[neillans]

What I tend to do in these cases is use a stored procedure, and then pass the version + script to it

Or, try replacing the intermediate go's with semi colons.

[greenstone]

Hi Neillans,

I have the same situation, but would prefer to keep my SQL script with the go's intact (your second suggestion).

I like your idea of using a stored procedure with the script and version. Could you post a sample of this stored procedure code and a sample script it uses? Is there any problem with the max-number-of-characters that a stored procedure parameter can handle?

Many thanks!