SQL Server Error Msg 111 – ‘CREATE FUNCTION’ must be the first statement in a query batch

SQL Server Error Message

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

Reason for the Error

When creating the User Defined function, if the CREATE FUNCTION is not the first statement in the query, you will receive this error.

For example , here’s a query that results in the ‘CREATE FUNCTION’ must be the first statement in a query batch.

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

CREATE FUNCTION [dbo].[ufn_GetYear] ()
RETURNS INT
AS
BEGIN
   RETURN YEAR(GETDATE())
END
GO

In the above query, we are checking if the user defined function exists, if so, we are dropping the function and then creating the function using CREATE FUNCTION statement. Since CREATE FUNCTION is not the first statement in the Query batch, it is failing with an Error.

Solution

To avoid this error, CREATE FUNCTION statement must always be the first statement in the Query batch. To fix this, the GO command needs to be added to seperate the DROP FUNCTION from the CREATE FUNCTION. One of the advantages of this is that the GO command lets the SQL Server know that its the end of the batch of T-SQL statements and anything after the GO is a new batch of query.

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

CREATE FUNCTION [dbo].[ufn_GetYear] ()
RETURNS INT
AS
BEGIN
   RETURN YEAR(GETDATE())
END
GO