Compile a function in an SSDT PreDeployment Script

Question

I have an unusual situation - first time!

I'm trying to write a pre-deployment script for an SSDT Project in Visual Studio, which will prepare a load of data for migration. There's a user-defined table-valued function (TF) in the new version of the database called [MySchema].[MyFunc] that will really help with the migration script... but it doesn't exist yet in the old database that will be upgraded and migrated. There are many other objects in the new version, but I just need to use this one to help with the migration. The function doesn't have any dependencies on any other new (or existing) objects, it's totally self-contained.

I'm hoping to compile [MySchema].[MyFunc] as part of the pre-deployment, so that I can use it. The function lives in .\MySchema\Functions\MyFunc.sql

I've attempted the following...

ATTEMPT 1

This fails with Incorrect syntax near CREATE (Note: CREATE is the first line of file .\MySchema\Functions\MyFunc.sql):

IF object_id('[MySchema].[MyFunc]', 'TF') IS NULL
BEGIN
    :r .\MySchema\Functions\MyFunc.sql
END

ATTEMPT 2

This fails with Incorrect syntax near 'GO' and Incorrect syntax near ':' Expecting CONVERSATION:

GO
IF object_id('[MySchema].[MyFunc]', 'TF') IS NULL
BEGIN
    :r .\MySchema\Functions\MyFunc.sql
    GO
END

ATTEMPT 3

Copy and paste the entire CREATE FUNCTION statement into my pre-deployment script:

CREATE FUNCTION [MySchema].[MyFunc]
(
    @p1 VARCHAR(255)
)
RETURNS @returntable TABLE
(
    some_col NVARCHAR(MAX)
)
AS
BEGIN
    -- some function code here
    RETURN
END

But this fails with CREATE FUNCTION must be the only statement in the batch. I've tried inserting GO before & after this, but I get similar results to ATTEMPT 2. I've also tried with ; instead of GO.

ATTEMPT 4

I tried to use an iTVF, but it didn't help

ATTEMPT 5

I considered, for a few brief moments, taking the code out of my function and just using it without a function... but I need to use that code around 20 times in the migration script. So I dismissed this option. It will produce a different result every time (due to changing parameters), so I can't just put it in a CTE or similar and re-use it each time.

PLEASE NOTE

Answers should be SSDT project specific. This isn't code being run in SSMS. It's in Visual Studio and will be run as part of a Publish process. If you're not sure what SSDT or a pre-deployment script is, please don't answer :-) Any answers not based on SSDT are irrelevant to this scenario. Thanks!

Answer 1

If you're targeting SQL Server 2016 or later two possible solutions come to mind:

1. in a seperate batch before including your function code try:

DROP FUNCTION IF EXISTS [MySchema].[MyFunc];

2. revise the content of the MyFunc.sql file such that it starts with:

CREATE OR ALTER FUNCTION [MySchema].[MyFunc]
...

References:

• DROP FUNCTION (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)

Answer 2

You can't have GO inside BEGIN/END. I don't see why Attempt 3 doesn't work, most probably because of other code you have in the same script. Normally you can create objects in the pre-script. Depending on the version of SQL Server you can either use IF EXISTS or change the code to DROP the function if it exists and then always CREATE it instead of the opposite approach you are trying to do.

Another way is to simply put data population logic to the pre-script itself. You can do something like:

IF NOT EXISTS (SELECT * FROM SomeTable)
BEGIN
  INSERT INTO SomeTable ...
END

In such case you'll be able to re-run this script without duplicating the data.