ALTER FUNCTION inside IF in SQL Server

Question

I need to alter a function only if it exists. I tried this:

IF EXISTS(SELECT * FROM Information_schema.Routines 
          WHERE Specific_schema = 'dbo' 
            AND SPECIFIC_NAME = 'fnTestFunc' 
            AND Routine_Type = 'FUNCTION')
BEGIN
    ALTER FUNCTION [dbo].[fnTestFunc] (@input VARCHAR(3))
    RETURNS VARCHAR(2)
    AS 
    BEGIN
         --something
    END
END

But shows an error

ALTER FUNCTION must be the single query in the batch

Any idea what is the issue here?

Answer 1

You have to make sure it's compiled and executed in a separate batch. The easiest way here is via sp_executesql:

IF EXISTS(SELECT * FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND SPECIFIC_NAME = 'fnTestFunc' AND Routine_Type = 'FUNCTION')
BEGIN

EXEC sp_executesql N'ALTER FUNCTION [dbo].[fnTestFunc] (@input VARCHAR(3))

RETURNS VARCHAR(2)

AS BEGIN

    --something
END'

END

This also avoids another problem - SQL Server wants to compile each batch before it starts executing it. But it'd fail to compile your original batch if fnTestFunc doesn't exist. You can't use a runtime check (the IF) to avoid a compile time error - unless you make sure, as above, that the compilation happens after the check is completed.