Seven times ALTER one DROP

image

It all started with the fact that I began to write the standard design T-SQL for my company. In this topic, I will focus on the construction of deleting an object before creating it.

Our team has about twenty SQL Ninja developers and everyone describes this construction in different ways, for example like this:

IF OBJECT_ID('dbo.Function', 'TF') IS NOT NULL
	DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

Or so:

IF EXISTS (
    SELECT * 
    FROM sys.objects 
    WHERE name = 'Procedure'
        AND type = 'P' 
)
    DROP PROCEDURE dbo.Procedure;
GO
CREATE PROCEDURE dbo.Procedure ..

And even so:

IF EXISTS (
    SELECT 1
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'dbo.Function')
        AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
)
    DROP FUNCTION dbo.Function;
GO
CREATE FUNCTION dbo.Function ..

And on StackOverflow most of all the likes gathered here is this option:

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

The stars went to meet me and I came across an implementation on one of the SQL sites, which at first outraged me, but then they told me that it was like this:

IF OBJECT_ID('dbo.Function', 'TF') IS NULL
    EXEC('CREATE FUNCTION dbo.Function() RETURNS @t TABLE(i INT) BEGIN RETURN END');
GO
ALTER FUNCTION dbo.Function ..

The fact is that if you do DROP and CREATE every time, then the rights to the object are deleted, and the object can also be in replication and when recreated, it will also be deleted from it.

In general, I liked this lambda decorator method and I decided to encapsulate it
in a procedure called dbo.antidrop.

The procedure has only two arguments, this is the name of the object and its type. You can see the type of your object like this:

SELECT type 
FROM sys.objects 
WHERE name = 'Name'

Here is how it will look as a result:

EXEC dbo.antidrop('dbo.Name', 'FN');
GO
ALTER FUNCTION dbo.Name ..

And of course, the code of the procedure itself:

IF OBJECT_ID('dbo.antidrop', 'P') IS NULL
    EXEC('CREATE PROC dbo.antidrop AS');
GO
CREATE PROC dbo.antidrop @name SYSNAME, @type SYSNAME
AS
BEGIN
    DECLARE @if_tf NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '() RETURNS @t TABLE(i INT) BEGIN RETURN END'');
        GO
    ';
    DECLARE @fn NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE FUNCTION ' + @name + '(@i INT) RETURNS INT AS BEGIN RETURN @i + 1 END'');
        GO
    ';
    DECLARE @p NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE PROC ' + @name + 'AS'');
        GO
    ';
    DECLARE @v NVARCHAR(512) = '
        IF OBJECT_ID(''' + @name + ''', ''' + @type + ''') IS NULL
            EXEC(''CREATE VIEW ' + @name + ' AS SELECT 1 AS i'');
        GO
    ';
    IF @type in (N'IF', N'TF')
    BEGIN
        EXEC(@if_tf);
    END
    ELSE IF @type = N'FN'
    BEGIN
        EXEC(@fn);
    END
    ELSE IF @type = N'P'
    BEGIN
        EXEC(@p);
    END
    ELSE IF @type = N'V'
    BEGIN
        EXEC(@v);
    END
END
GO

Thanks for attention!

Also popular now: