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!

`ALTER/CREATE FUNCTION` and `ALTER/CREATE PROCEDURE` need to be statements in their own separate batch, i.e.: you can't wrap them in `IF` statements. — AlwaysLearning, Sep 26 '21 at 00:48
When I had to go down that rabbit hole (migrating data into the new schema as a part of SSDT deployment), I put all the migration logic into the post-deployment script. At this point, your function will be already available. — Roger Wolf, Sep 27 '21 at 00:02
When I had to do this for some migrations-style scripts (similar concept though), I had to rewrite that into a dynamic SQL call that would create the function. It was a pain to do, but for something that will be re-used regularly, it makes sense. Because that had to be the first command in a batch, you may want to look at the option of creating it w/ dynamic SQL. Alternatively, wrap your deployment in a larger script and call that before calling the publish action. Not fun, but sometimes necessary if the function doesn't yet exist, but is referenced. — Peter Schott, Sep 27 '21 at 15:37

score 1 · Answer 1 · answered Sep 26 '21 at 01:52

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

in a seperate batch before including your function code try:

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

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

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

References:

score 0 · Answer 2 · answered Sep 27 '21 at 07:59

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.

Compile a function in an SSDT PreDeployment Script

2 Answers2