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

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


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.


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.

Tags:

Sql

Sql Server

Tsql

Recent Posts