SQL Server Error Messages - Msg 111

Error Message

Server: Msg 111, Level 15, State 1, Line 1
'CREATE FUNCTION' must be the first statement in a query batch.

Causes

As the error message suggests, the CREATE FUNCTION statement must be the first statement in a query batch. There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch.

To illustrate, here’s a script that will generate the error message:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[ufn_IsLeapYear]') AND
                 XTYPE IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[ufn_IsLeapYear]


CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @InputDate    DATETIME )
RETURNS BIT
AS
BEGIN

    IF (YEAR( @InputDate ) % 4 = 0 AND YEAR( @InputDate ) % 100 != 0) OR
        YEAR( @InputDate ) % 400 = 0
        RETURN 1

    RETURN 0

END
GO

Msg 111, Level 15, State 1, Line 7
'CREATE FUNCTION' must be the first statement in a query batch.

When scripting a user-defined function, it is a common practice to check first if the function already exists and if it already exists, drop the function first using the DROP FUNCTION statement before creating it using the CREATE FUNCTION statement. This can also be done using the ALTER FUNCTION statement. The ALTER FUNCTION statement alters an existing Transact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures or triggers. The only drawback with the ALTER FUNCTION is that it will fail if the function does not exist yet. With the DROP FUNCTION/CREATE FUNCTION combination, the script will always succeed whether the function already exists or not.

As the message suggests, to avoid this error the CREATE FUNCTION statement must be the first statement in a query batch. To make the CREATE FUNCTION statement the first statement in the script above, the GO command can be added to separate the DROP FUNCTION statement from the CREATE FUNCTION statement.

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[ufn_IsLeapYear]') AND
                 XTYPE IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[ufn_IsLeapYear]
GO

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @InputDate    DATETIME )
RETURNS BIT
AS
BEGIN

    IF (YEAR( @InputDate ) % 4 = 0 AND YEAR( @InputDate ) % 100 != 0) OR
        YEAR( @InputDate ) % 400 = 0
        RETURN 1

    RETURN 0

END
GO

The GO command signals the end of a batch of Transact-SQL statements and any statements after the GO command signals the beginning of a new batch of queries or Transact-SQL statements. By adding the GO command after the DROP function statement, the CREATE FUNCTION statement now becomes the first statement in the succeeding query batch.

• Frequently Asked Questions - SQL Server Error Messages
• Tips & Tricks - SQL Server Error Messages 1 to 500