I am writing a script, that updates the database of my application to the next version.
As part of the latest version updgrade, i added a regex function as a CLR User Defined Function out of an assembly. Since this script will run with every upgrade, i need to check if the function exists before calling CREATE FUNCTION.
My initial idea was like this:
IF NOT EXISTS ( SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Regex'
AND Routine_Type = 'FUNCTION' )
BEGIN
CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) RETURNS [nvarchar](4000) AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
END
GO
However this returns the error
Incorrect Syntax: 'CREATE FUNCTION' must be the only statement in the batch
I cannot put GO before and after the CREATE FUNCTION call, since this breaks the BEGIN and END clause apart, giving multiple errors, like
Could not find stored procedure
The tried the solution in the question Creating a UDF(User Define Function) if is does not exist and skipping it if it exists, inverting the check, dropping it if existent and then creating it again:
IF EXISTS ( SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Regex'
AND Routine_Type = 'FUNCTION' )
BEGIN
DROP FUNCTION [Regex]
END
GO
CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) RETURNS [nvarchar](4000) AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
GO
However since later there are multiple constraints using the function, when calling this again i get the following errors, rendering the answer of the related question useless for my usecase:
Msg 3729, Level 16, State 1, Line 41
Cannot DROP FUNCTION 'Regex' because it is being referenced by object 'RX_T_DataManagementItems_ItemIdent'.
Msg 2714, Level 16, State 51, Procedure regex, Line 1 [Batch Start Line 43]
There is already an object named 'regex' in the database.
The script runs smooth even with the errors showing, but i would like to not have any error messages, if there are no 'real' errors.
Is there a way to 'outsource' the batch containing the CREATE FUNCTION call, that can be called within the conditional path or some other way to implement this properly?
Hi you can try using EXEC:
The IF NOT EXISTS check just makes sure the function isn’t already there. If it’s missing, CREATE FUNCTION runs dynamically using EXEC(), so it gets its own batch. Since EXEC runs separately, it avoids any issues with the single-batch rule.
IF NOT EXISTS (SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Regex'
AND Routine_Type = 'FUNCTION')
BEGIN
EXEC('CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000))
RETURNS [nvarchar](4000)
AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]');
END