Creating a UDF(User Define Function) if is does no

问题:

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!

回答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

回答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.

回答3:

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.

回答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

回答5:

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.