Создание UDF (функция определения пользователя), если она не существует, и пропускать ее, если она существует

Ответ 1

Самый простой способ решить это - фактически удалить функцию, если она уже существует, а затем повторно создать ее:

/* 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

Обратите внимание на использование функции "object_id" в приведенном выше. На самом деле это довольно распространенный способ проверить существование объекта, хотя он подвержен определенным ограничениям.

Подробнее об этом можно прочитать здесь: OBJECT_ID

Ответ 2

Как я долго бил головой о эту кирпичную стену, я подниму еще два цента.

Как уже было сказано, да, было бы неплохо добавить его, только если его еще нет, но это невозможно в T-SQL без использования динамического SQL... и обертывания ваших функций, процедур, триггеров, взгляды и, возможно, еще более неясные объекты, поскольку динамические заявления просто слишком непрактичны. (Не просите меня поддерживать исходный код, который может содержать более 4 отдельных апострофов подряд!)

Отбрасывание (если оно существует) и (повторное) создание является жизнеспособным решением. Предположительно, если вы создаете новый код, вы бы захотели создать объект, если он еще не был там, и в противном случае отказаться от существующего/старого кода и заменить его новым. (Если вы случайно замените "новый" код "старым" кодом, у вас есть проблема с управлением версиями, которая является другой и более сложной темой.)

Реальная проблема - потеря информации при отбрасывании старого кода. Какая информация? Тот, который я часто нажимаю, - это права доступа: у кого есть EXECUTE или, для некоторых функций, SELECT права на объект? Бросьте и замените, и они исчезли. Ответ на этот вопрос, конечно же, заключается в script правах доступа как части развертывания script. Однако, если у вас есть ситуация, когда разные среды размещения базы данных имеют разные конфигурации (логины, домены, группы и т.д. И т.д.), Вы можете оказаться в ситуации, когда вы не сможете и не можете знать, какие существующие права доступа на данном экземпляре, поэтому, если вы просто снимете и заново создадите его, существующие пользователи больше не смогут получить к нему доступ. (Расширенные свойства и другие биты эзотерики будут одинаково затронуты.)

Первое и лучшее исправление для этого - обеспечить надежную защиту. Настройте роли базы данных, назначьте/сопоставьте соответствующие разрешения для ролей, тогда вам не нужно будет знать, кто в ролях - это будет работа администраторов среды. (Вы все равно должны иметь что-то вроде GRANT EXECUTE on ThisProc to dbo.xxx в конце вашего script, но это не так сложно.

Если, как и я, вы (а) не наделены полномочиями для развертывания хорошей и надежной модели безопасности и (б) ленивы и, вероятно, не должны проверять конец сохраненных в сотнях строк строк файл процедуры для кода прав доступа, вы можете сделать что-то вроде следующего. (Это задано для хранимых процедур, но подходит для функций и других объектов.)

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

Это будет:

• Сначала проверьте, существует ли процедура. Если это не так, создайте "placholder" и установите для него соответствующие права доступа.
• Затем, независимо от того, существовал ли он до запуска script, ALTER и установите его с нужным кодом.

Также существует проблема управления объектами на основе кода (в основном хранимыми процедурами) в схемах, где схемы могут отсутствовать. Я еще не понял, что один из них, и если вам повезет, вы никогда не столкнетесь с подобной ситуацией.

Ответ 3

Сообщение об ошибке в точности верно, что операторы CREATE FUNCTION должны быть первыми в пакете, что означает, что, к сожалению, вы не можете сделать:

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

Я обычно делаю в этой ситуации:

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

CREATE FUNCTION dbo.myFunction (
    ...
)
GO

Обратите внимание, что я обычно использую функцию object_id(), поскольку она проще, легче читать и более надежна, чем EXISTS (SELECT * FROM sys.whatever).

Конечно, это решение будет работать только для вас, если вы в порядке, всегда переписывая любое предыдущее определение функции. Если это не так, сообщите мне.

Ответ 4

На самом деле это работает в 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