sql - tabla - Crear una UDF(función de definición de usuario) si no existe y omitirla si existe

listado de funciones en sql server

Question

Hola y gracias por leer esto.

Estoy intentando usar la sentencia IF EXISTS / IF NOT EXISTS para verificar si existe un objeto. Básicamente quiero omitirlo si está ahí o crearlo si no está allí.

Escribí el código de dos maneras diferentes, pero recibo un error: Crear función debe ser la única función en el lote. Si coloco GO entre las declaraciones como se ilustra a continuación, recibo otra advertencia: Sintaxis incorrecta cerca de GO.

¿A dónde me voy mal aquí?

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

O esto:

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

Gracias por ver esto!

Answer 1

Como he golpeado mi cabeza en esta pared de ladrillo durante mucho tiempo, lanzaré dos centavos más.

Como se señaló, sí, sería bueno agregarlo solo si no está ya allí, pero eso no es posible en T-SQL sin usar SQL dinámico ... y envolviendo sus funciones, procedimientos, disparadores, vistas, y tal vez objetos aún más oscuros como declaraciones dinámicas es simplemente demasiado impráctico. (¡No me pida que admita el código fuente que puede contener más de 4 apóstrofes en una fila!)

Dejar caer (si existe) y (re) crear es una solución viable. Presumiblemente, si está desplegando un nuevo código, desearía crear el objeto si aún no estaba allí, y de lo contrario, eliminar el código existente / antiguo y reemplazarlo por el nuevo. (Si pudiera reemplazar accidentalmente el código "nuevo" con el código "viejo", tiene un problema de control de versión, que es un tema diferente y mucho más difícil).

El problema real es la pérdida de información cuando se elimina el código anterior. ¿Que información? El que a menudo golpeo son los derechos de acceso: ¿quién tiene EXECUTE o, para algunas funciones, SELECT derechos sobre el objeto? Tira y reemplaza, y se han ido. La respuesta a esto, por supuesto, es hacer un script de los derechos de acceso como parte del script de implementación. Sin embargo, si tiene una situación en la que diferentes entornos de alojamiento de bases de datos tienen configuraciones diferentes (inicio de sesión, dominios, grupos, etc.), es posible que se encuentre en una situación en la que no sabrá cuáles son los derechos de acceso existentes. en una instancia determinada, por lo que si simplemente la suelta y la vuelve a crear, es posible que los usuarios existentes ya no puedan acceder a ella. ( Las propiedades extendidas y otros bits de esoterica se verían afectados de manera similar ) .

La primera y mejor solución para esto es implementar una seguridad robusta. Configure los roles de la base de datos, asigne / asocie los permisos apropiados a los roles, entonces no tendrá que saber quién está en los roles, ese sería el trabajo de los administradores del entorno. (Todavía tendría que tener algo como GRANT EXECUTE on ThisProc to dbo.xxx al final de su script, pero eso no es tan difícil.

Si, como yo, (a) no se le ha dado la autorización para implementar un modelo de seguridad bueno y sólido, y (b) es perezoso y es probable que no compruebe el final de un archivo de procedimiento almacenado de cientos de líneas para código de derechos de acceso, puede hacer algo como lo siguiente. (Esto se establece para procedimientos almacenados, pero es adaptable para funciones y otros objetos).

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

Esta voluntad:

• Primero verifique si existe el procedimiento. Si no es así, cree un "titular" y configure los derechos de acceso apropiados para él
• Luego, si existió o no antes de que se ejecutara el script, ALTER y ALTER con el código deseado.

También existe el problema de administrar objetos basados ​​en código (principalmente procedimientos almacenados) en esquemas donde los esquemas pueden no existir. Todavía tengo que descubrirlo, y si tienes suerte, nunca terminarás en una situación igualmente extraña.

Answer 2

El mensaje de error es exactamente correcto, que las instrucciones CREATE FUNCTION deben ser las primeras en un lote, lo que significa que desafortunadamente no puede hacer:

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

Lo que suelo hacer en esta situación es:

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

CREATE FUNCTION dbo.myFunction (
    ...
)
GO

Tenga en cuenta que generalmente uso la función object_id () porque es más simple, fácil de leer y más robusta que EXISTS (SELECT * FROM sys.whatever).

Por supuesto, esta solución solo funcionará para usted si está de acuerdo con sobrescribir siempre cualquier definición previa de la función. Si eso no está bien en su situación, hágamelo saber.

Answer 3

En realidad esto funciona en 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 4

La forma más fácil de resolver esto es eliminar la función si ya existe, y luego volver a crearla:

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

Tenga en cuenta el uso de la función ''object_id'' en lo anterior. Esta es en realidad una forma bastante común de verificar la existencia de un objeto, aunque está sujeto a ciertas restricciones.

Puedes leer más sobre esto aquí: OBJECT_ID

Answer 5

Nigromancia.
Dejar caer no es una buena idea, ya que puede haber permisos establecidos en un objeto.

Por lo tanto, la forma correcta de hacerlo sería en realidad
A) Crea la función si no existe (ficticia)
B) ALTERAR la función si ya existe. (Puede que no esté actualizado)

Ejemplo:

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

-- 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].[tfu_VWS_PDF_LegendeTitelDaten]'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT'')) 
BEGIN
    -- CREATE FUNCTION dbo.[tfu_FOOFOO]() RETURNS int AS BEGIN RETURN 123 END 
    -- CREATE FUNCTION dbo.[tfu_FOOFOO]() RETURNS table AS RETURN (SELECT * FROM information_schema.tables)  
    EXECUTE(''
        CREATE FUNCTION dbo.[tfu_FOOFOO]() RETURNS int AS BEGIN RETURN 123 END 
    '')
END 
GO



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

Tenga en cuenta que no puede cambiar una función con valores de tabla a una función escalar o viceversa.
Sin embargo, puede cambiar los tipos de argumentos y la cantidad de argumentos, así como el esquema de devolución, a voluntad.