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.