Creating a UDF(User Define Function) if is does not exist and skipping it if it exists

Question

Hi and thanks for reading this.

I am trying to use the IF EXISTS/IF NOT EXISTS statement to check if an Object exist. Basically I want to skip it if it is there or create it if it is not there.

I have writing the code in two different ways but I get an error: Create function must be the only function in the batch. If I place GO between the statements as Illustrated below, I get another warning: Incorrect Syntax near GO.

Where am I going wrong here?

IF NOT EXISTS
(select * from Information_schema.Routines where SPECIFIC_SCHEMA='dbo' 
AND SPECIFIC_NAME = 'FMT_PHONE_NBR' AND Routine_Type='FUNCTION')

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Or this:

IF NOT EXISTS
(SELECT name FROM sys.objects WHERE name = 'dbo.FMT_PHONE_NBR')

GO

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Thanks for checking this out!

Answer 1

The easiest way to solve this is actually to delete the function if it already exists, and then re-create it:

/* If we already exist, get rid of us, and fix our spelling */
IF OBJECT_ID('dbo.FMT_PHONE_NBR') IS NOT NULL
  DROP FUNCTION FMT_PHONE_NBR
GO

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Note the usage of the 'object_id' function in the above. This is actually a pretty common way to check for the existence of an object, although it is subject to certain constraints.

You can read more about it here: OBJECT_ID

Answer 2

As I've beaten my head on this brick wall for a long time, I'll toss in two more cents.

As pointed out, yes, it'd be nice to add it only if it isn't already there, but that just not possible in T-SQL without using dynamic SQL... and wrapping your functions, procedures, triggers, views, and maybe even more obscure objects as dynamic statements is just too darn impractical. (Don't ask me to support source code that might contain more than 4 single apostrophes in a row!)

Dropping (if it exists) and (re)creating is a viable solution. Presumably, if you are rolling out new code, you would want to create the object if it was not already there, and otherwise drop the existing/old code and replace it with the new. (If you might accidentally replace "new" code with "old" code, you have a version control problem, which is a different and much harder topic.)

The real problem is losing information when you drop the old code. What information? The one I often hit is access rights: who has EXECUTE or, for some functions, SELECT rights on the object? Drop and replace, and they're gone. The answer to this, of course, is to script the access rights as part of the deployment script. However if you have a situation where different database-hosting environments have different configurations (logins, domains, groups, etc. etc.), you might be in a situation where you won't and can't know what the existing access rights are on a given instance, so if you just drop and recreate it, existing users may no longer be able to access it. (Extended properties and other bits of esoterica would similarly affected.)

The first and best fix for this is to implement robust security. Set up database roles, assign/associate appropriate permissions to the roles, then you won't have to know who's in the roles--that'd be the job of the environment administrators. (You'd still have to have something like GRANT EXECUTE on ThisProc to dbo.xxx at the end of your script, but that's not so hard.

If, like me, you (a) haven't been empowered to roll out a good and robust security model, and (b) are lazy and likely to not check the end of a hundreds-of-lines-long stored procedure file for access rights code, you can do something like the following. (This is set for stored procedures, but is adaptible for functions and other objects.)

-- isProcedure
-- IsScalarFunction    (Returns single value)
-- IsTableFunction     (Declared return table structure, multiple statements)
-- IsInlineFunction    (Based on single select statement)
-- IsView

IF objectproperty(object_id('dbo.xxx'), 'isProcedure') is null
 BEGIN
    --  Procedure (or function) does not exist, create a dummy placeholder
    DECLARE @Placeholder varchar(100)
    SET @Placeholder = 'CREATE PROCEDURE dbo.xxx AS RETURN 0'
    EXEC(@PlaceHolder)

    --  Configure access rights
    GRANT EXECUTE on dbo.xxx TO StoredProcedureUser
 END
GO

ALTER PROCEDURE dbo.xxx
(etc.)
GO

This will:

• First check if the procedure exists. If it doesn't, create a "placholder", and set up the appropriate access rights to it
• Then, whether or not it existed before the script was run, ALTER and set it with the desired code.

There's also the problem of managing code-based objects (primarily stored procedures) in schemas where the schemas might not exist. I've yet to figure that one out, and if you're lucky, you'll never end up in a similarly oddball situation.

Answer 3

Necromancing.
Dropping isn't a good idea, as there might be permissions set on an object.

Therefore, the proper way to do it would actually be to
A) Create the function if it doesn't exist (dummy)
B) ALTER the function if it already exists. (it might not be up-to-date)

Example:

-- DROP FUNCTION IF EXISTS [dbo].[TestFunction]

-- Do not drop the function if it exists - there might be privileges granted on it... 
-- You cannot alter function from table-valued function to scalar function or vice-versa 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) 
BEGIN
    -- CREATE FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 123 END 
    -- CREATE FUNCTION dbo.[TestFunction]() RETURNS table AS RETURN (SELECT * FROM information_schema.tables)  
    EXECUTE('
        CREATE FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 123 END 
    ')
END 
GO



-- ALTER FUNCTION dbo.[TestFunction](@abc int) RETURNS table AS RETURN (SELECT * FROM information_schema.tables)  
ALTER FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 'test' END 

Note that you can't change a table-valued function into a scalar function or vice-versa.
You can however change the arguments types and number of arguments - as well as the return schema - at will.

Answer 4

Actually this works in 2008

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetTZDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    execute dbo.sp_executesql @statement = N'
        CREATE FUNCTION [dbo].[fn_GetTZDate] ()

        RETURNS datetime
        AS -- WITH ENCRYPTION AS
        BEGIN
            -- Declare the return variable here
            DECLARE @tzadj int, @sysdate datetime
            SET @sysdate = getdate()
            SET @tzadj = 0
            SELECT @tzadj = [tzAdjustment] FROM USysSecurity WHERE [WindowsUserName] = SYSTEM_USER
            if @tzadj <> 0
            BEGIN
                SET @sysdate = dateadd(hh, @tzadj, @sysdate)
            END

            -- Return the result of the function
            RETURN @sysdate

        END    ' 
END

GO

Answer 5

The error message is exactly right, that CREATE FUNCTION statements must the first in a batch, which means that unfortunately you can't do:

IF [condition]
BEGIN
    CREATE FUNCTION
    ...
END
GO

What I usually do in this situation is:

IF object_id('dbo.myFunction') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.myFunction
END
GO

CREATE FUNCTION dbo.myFunction (
    ...
)
GO

Note that I usually use the object_id() function as it's simpler, easier to read, and more robust than EXISTS (SELECT * FROM sys.whatever).

Of course, this solution will only work for you if you're OK with always overwriting any previous definition of the function. If that isn't OK in your situation, let me know.

Answer 6

TL;DR

Here is the op's use-case (dbo.sp_insertoralter procedure is given as a factory just after Below):

EXEC dbo.sp_insertoralter '[dbo].[FMT_PHONE_NBR]', '(@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 4, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 7, 4)
END', 0, 'IsScalarFunction'

print [dbo].[FMT_PHONE_NBR] ('987654132456') --  987-654-1324

Warning: Please do keep in mind that it's not intended to be a general-purpose factory, as user rights may not apply same everywhere, and there are tons of possible sql injections.

---

Original answer

Starting from @PhilipKelley's answer, if you wish to use a schema other than 'dbo', see Below. No temp var are required to make EXEC('sql code') instructions working by themselves.

For the sake of the @obj_lookup reuse, you may uncomment the last EXEC('PRINT...') part.

Important note: Everything that is stringified will not have spellcheck enabled. It's up to you to be sure that names (schema and function) are accurate, and any db rework might break names/schemas without spellcheck notice.

I strongly recommend to read the full @PhilipKelley's answer, especially for the "Don't ask me to support source code that might contain more than 4 single apostrophes in a row!" part.

Reminder: This is not a concurrent-safe operation, as parallel executions might fail not being able to create the same object (concurrently in creation), and/or will result in an unknown object's altered body (presumably the last one to succeed). As are all the other "drop-then-create" answers, but the warn might not harm.

Below:

-- IsProcedure
-- IsScalarFunction    (Returns single value)
-- IsTableFunction     (Declared return table structure, multiple statements)
-- IsInlineFunction    (Based on single select statement)
-- IsView
-- (Full list available at https://learn.microsoft.com/fr-fr/sql/t-sql/functions/objectproperty-transact-sql?view=sql-server-ver15 )

DECLARE @obj_lookup nvarchar (max) = 'abc.xxx'
DECLARE @obj_alter nvarchar (max) = '() --  Object parameters come here
    RETURNS INT AS BEGIN --  Actual body goes here
        RETURN 0
    END'
DECLARE @obj_oralter bit = 0
DECLARE @obj_type nvarchar(100) = 'IsScalarFunction'
DECLARE @obj_application nvarchar(100) = 'FUNCTION'

IF objectproperty(object_id(@obj_lookup), @obj_type) is null
 --  Here is the "create if not exists" behaviour
 BEGIN
    EXEC('CREATE FUNCTION ' + @obj_lookup + @obj_alter)
    --  Configure access rights
    EXEC('GRANT EXECUTE on ' + @obj_lookup + ' TO StoredProcedureUser')
 END
ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
    EXEC('ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter) --  Untouched access rights
/* --  Alternatively, you may prefer this 'CREATE OR ALTER' instruction to avoid the above objectproperty [if/else if] block:
EXEC('CREATE OR ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
*/
GO

-- Actual code (considering the object now exists or was altered)
PRINT abc.xxx()
(..etc)
/* -- For the "@obj_lookup" reuse:
EXEC('PRINT ' + @obj_lookup + '()
(..etc)')
*/
GO

---

Factory

That can be wrapped in a procedure (yay):

CREATE PROCEDURE dbo.sp_insertoralter(@obj_lookup as nvarchar(max), @obj_alter as nvarchar(max), @obj_oralter as bit = 0, @obj_type as nvarchar(100))
 AS BEGIN
    --  Type preparation
    declare @obj_application nvarchar(100) = case
            when @obj_type = 'IsProcedure' then 'PROCEDURE'
            when @obj_type = 'IsScalarFunction' or @obj_type = 'IsTableFunction' or @obj_type = 'IsInlineFunction' then 'FUNCTION'
            when @obj_type = 'IsView' then 'VIEW'
            when @obj_type = 'IsTable' or @obj_type = 'IsUserTable' then 'TABLE'
            when @obj_type = 'IsTrigger' then 'TRIGGER'
            else null --  Restriction to known (usefull) cases
        end
    if @obj_application is null
    begin
        raiserror ('An invalid @obj_type was specified for procedure', 10, 1); --throw 51000, 'An invalid @obj_type was specified for procedure', 1;
        return
    end

    IF objectproperty(object_id(@obj_lookup), @obj_type) is null
     BEGIN --  Here is the "create if not exists" behaviour
        EXEC('CREATE ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
        --  Configure access rights
        EXEC('GRANT EXECUTE on ' + @obj_lookup + ' TO StoredProcedureUser')
     END
    ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
        EXEC('ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter) --  Untouched access rights
    /* --  Alternatively, you may prefer the 'CREATE OR ALTER' instruction to avoid the above objectproperty [if/else if] block:
    EXEC('CREATE OR ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
    */
 END
GO

As you might have noticed, that procedure can then be self-invoked (but has to exist to be invoked), so here is an auto-use-case (double yay):

--  The "dbo.sp_insertoralter" procedure has to exist (or whatever name you gave it), let's pretend someone manually executed the `Below:` part with these parameters:
EXEC dbo.sp_insertoralter 'dbo.sp_insertoralter', '(@obj_lookup as nvarchar(max), @obj_alter as nvarchar(max), @obj_oralter as bit = 0, @obj_type as nvarchar(100))
 AS BEGIN
    --  Type preparation
    declare @obj_application nvarchar(100) = case
            when @obj_type = ''IsProcedure'' then ''PROCEDURE''
            when @obj_type = ''IsScalarFunction'' or @obj_type = ''IsTableFunction'' or @obj_type = ''IsInlineFunction'' then ''FUNCTION''
            when @obj_type = ''IsView'' then ''VIEW''
            when @obj_type = ''IsTable'' or @obj_type = ''IsUserTable'' then ''TABLE''
            when @obj_type = ''IsTrigger'' then ''TRIGGER''
            else null --  Restriction to known (usefull) cases
        end
    if @obj_application is null
    begin
        raiserror (''An invalid @obj_type was specified for procedure'', 10, 1); --throw 51000, ''An invalid @obj_type was specified for procedure'', 1;
        return
    end

    IF objectproperty(object_id(@obj_lookup), @obj_type) is null
     BEGIN --  Here is the "create if not exists" behaviour
        EXEC(''CREATE '' + @obj_application + '' '' + @obj_lookup + @obj_alter)
        --  Configure access rights
        EXEC(''GRANT EXECUTE on '' + @obj_lookup + '' TO StoredProcedureUser'')
     END
    ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
        EXEC(''ALTER '' + @obj_application + '' '' + @obj_lookup + @obj_alter) --  Untouched access rights
    /* --  Alternatively, you may prefer the ''CREATE OR ALTER'' instruction to avoid the above objectproperty [if/else if] block:
    EXEC(''CREATE OR ALTER '' + @obj_application + '' '' + @obj_lookup + @obj_alter)
    */
 END', 1, 'IsProcedure'

And now, here is the op's use-case (triple yay):

EXEC dbo.sp_insertoralter '[dbo].[FMT_PHONE_NBR]', '(@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 4, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 7, 4)
END', 0, 'IsScalarFunction'

print [dbo].[FMT_PHONE_NBR] ('987654132456') --  987-654-1324

Warning: Please do keep in mind that it's not intended to be a general-purpose factory, as user rights may not apply same everywhere, and there are tons of possible sql injections.

Answer 7

Starting with SQL Server 2016 Service Pack 1 it is possible to directly CREATE OR ALTER a FUNCTION:

CREATE OR ALTER FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END
GO

No drop, no dynamic SQL, this should be used if SQL Server 2016 SP1 is available.