Liquibase Formatted SQL and SQL Server Stored Procedures

Question

My team is trying to get Liquibase-formatted SQL to work with SQL Server Enterprise. We have no problem creating tables, but whenever we try to create a stored procedure we get parsing errors.

This is our script:

--liquibase formatted sql
--changeset <AUTHOR>:<ID> logicalFilePath:<MY_PATH> dbms:mssql failOnError:true splitStatements:true runOnChange:true endDelimiter:;

USE [test_db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [test_schema].[test_proc]
    @grgr_ck NVARCHAR(30) = NULL,
    @cspi_id NVARCHAR(30) = NULL,
    @nwst_pfx NVARCHAR(30) = NULL,
    @cscs_id NVARCHAR(30) = NULL,
    @cspi_eff_dt NVARCHAR(30) = NULL
AS
SELECT GRGR_CK,
       GRGR_ID,
       CSCS_ID,
       CSPI_ID,
       CSPI_EFF_DT = CONVERT(CHAR(10),CSPI_EFF_DT,101),
       CECN_EFF_DT = CONVERT(CHAR(10),CECN_EFF_DT,101),
       CECN_TERM_DT = CONVERT(CHAR(10),CECN_TERM_DT,101),
       ECN_ID,
       NWST_PFX,
       INSERT_DT = CONVERT(CHAR(10),INSERT_DT,101),
       LAST_UPD_BY,
       LAST_UPD_DT = CONVERT(CHAR(10),LAST_UPD_DT,101)
FROM test_schema.test_table
WHERE GRGR_CK =
(
    SELECT CONVERT(INT, @grgr_ck)
)
      AND CSPI_ID = @cspi_id
      AND NWST_PFX = @nwst_pfx
      AND CSCS_ID = @cscs_id
      AND CSPI_EFF_DT =
      (
          SELECT CONVERT(DATETIME, @cspi_eff_dt)
      )
      AND CECN_TERM_DT < '12/31/9999';

And this is the error:

[ERROR] Reason: liquibase.exception.DatabaseException: Incorrect syntax near '12/31/9999'.

Semicolons break it every time. We've tried using other delimiters like \ but those haven't worked either. Can anyone show us the correct way to create a stored procedure in SQL Server using Liquibase-formatted SQL? (For the record, we are able to create procedures with Liquibase XML files, but our developers want to use "pure" SQL)

---

Answer 1

Your script includes SQLCMD statements ("USE", "SET ANSI_NULLS" and "SET QUOTED_IDENTIFIER").

Since Liquibase is executing via JDBC, the SQLCMD statements will not execute through that path.

There are two options:

1. Remove the SQLCMD statements from your script (as shown below), or
2. Use the SQLCMD option in Liquibase so you can execute your original SQLCMD script

Option 1

To run this script via JDBC, you need to:

• Remove the SQLCMD statements from your formatted SQL changelog

You could also simplify the changeset attributes:

• Set splitStatements:false (best practice: use only one CREATE PROCEDURE per changeset)
• Remove the endDelimiter since you set splitStatements:false
• Remove the logicalFilePath as it's only needed for more advanced use-cases.
• Remove failOnError:true since this is the default
• Remove runOnChange:true unless you were expecting to have liquibase re-run this changeset if the CREATE PROCEDURE changed. If you want to do that, then you'll need to change the SQL to be "create or replace"

Here is your updated changelog:

--liquibase formatted sql
--changeset MyName:ID-00001 splitStatements:false dbms:mssql

CREATE PROCEDURE [test_schema].[test_proc]
    @grgr_ck NVARCHAR(30) = NULL,
    @cspi_id NVARCHAR(30) = NULL,
    @nwst_pfx NVARCHAR(30) = NULL,
    @cscs_id NVARCHAR(30) = NULL,
    @cspi_eff_dt NVARCHAR(30) = NULL
AS
SELECT GRGR_CK,
       GRGR_ID,
       CSCS_ID,
       CSPI_ID,
       CSPI_EFF_DT = CONVERT(CHAR(10),CSPI_EFF_DT,101),
       CECN_EFF_DT = CONVERT(CHAR(10),CECN_EFF_DT,101),
       CECN_TERM_DT = CONVERT(CHAR(10),CECN_TERM_DT,101),
       ECN_ID,
       NWST_PFX,
       INSERT_DT = CONVERT(CHAR(10),INSERT_DT,101),
       LAST_UPD_BY,
       LAST_UPD_DT = CONVERT(CHAR(10),LAST_UPD_DT,101)
FROM test_schema.test_table
WHERE GRGR_CK =
(
    SELECT CONVERT(INT, @grgr_ck)
)
      AND CSPI_ID = @cspi_id
      AND NWST_PFX = @nwst_pfx
      AND CSCS_ID = @cscs_id
      AND CSPI_EFF_DT =
      (
          SELECT CONVERT(DATETIME, @cspi_eff_dt)
      )
      AND CECN_TERM_DT < '12/31/9999';

Answer 2

It looks like you set your splitStatements to true, which causes SQL to automatically split your queries on ; and GO. That's not what you want in this case.

You can either set the attribute splitStatements to false or use the refactoring tag instead.

Documentation:

1. https://docs.liquibase.com/change-types/create-procedure.html
2. https://docs.liquibase.com/change-types/sql.html
3. https://docs.liquibase.com/change-types/sql-file.html
4. https://docs.liquibase.com/concepts/changelogs/attributes/enddelimiter-sql-attribute.html