CODEWITHSUNDEEP
sql-serverliquibase
Aaron
Aaron

Reputation: 101

Run different liquibase scripts based on SQL Server version

I am trying to use liquibase to add members to a role, but the SQL is different from SQL Server 2008 to 2012.

SQL Server 2008:

exec sp_addrolemember db_datareader, MYUSER

SQL Server 2012:

ALTER ROLE db_datawriter ADD MEMBER MYUSER

The following SQL when run via liquibase against SQL Server 2012 works correctly, but when ran against SQL Server 2008 it fails and says:

Incorrect syntax near the keyword 'ADD'

Code:

DECLARE @ver nvarchar(50),
        @intVer int,
        @ver2008 int = 10

SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @intVer = CAST(SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) AS int)

IF (@intVer = @ver2008)
    exec sp_addrolemember db_datawriter, MYUSER
ELSE
    ALTER ROLE db_datawriter ADD MEMBER MYUSER;

I tried separating the versions, putting SQL Server 2008 in one file and 2012 in another and using a 'precondition' at the top of the changelog (outside of a changeset). But there are only 2 OnFail options, HALT, which stops the entire update, or WARN, which just continues the script, both of which do not give me what I need.

I need to be able for the script to figure out which version of SQL Server it is working with and only run that particular liquibase script, whether it be a changeset or changelog.

Upvotes: 0

Views: 453

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

The compilation of the entire batch will fail if the syntax is unrecognized. You can work around the issue by wrapping the statements in EXECUTE or by using sp_executesql.

IF (@intVer = @ver2008)
    EXECUTE(N'exec sp_addrolemember db_datawriter, MYUSER;')
ELSE
    EXECUTE(N'ALTER ROLE db_datawriter ADD MEMBER MYUSER;');

Upvotes: 1

Related Questions