Error:create function must be the only statement in the batch

Question

I am trying to firstly check if the function exists then create it, if it doesn't exist.

I'm getting this error from the function:

IF NOT EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[GetRelativeExpiry]') AND type in (N'U'))
    BEGIN

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    CREATE FUNCTION [dbo].[GetRelativeExpiry]
    (
        @Date DATE,
        @N INT
    )
    RETURNS DATE
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @Expiry as DATE;

        IF @N > 0
        BEGIN
            SELECT @Expiry = MAX(E2.Expiry) 
            FROM (SELECT TOP(@N) Expiry
                  FROM ExpiryDates E1
                  WHERE E1.Expiry >= @date
                  ORDER BY E1.Expiry) AS E2
        END
        ELSE
        BEGIN
            SELECT @Expiry = MIN(E2.Expiry) 
            FROM (SELECT TOP(-@N) Expiry
                  FROM ExpiryDates E1
                  WHERE E1.Expiry <= @date
                  ORDER BY E1.Expiry DESC) AS E2
        END
        RETURN @Expiry

    END

    END

I am not sure why I am getting this error, could someone please help? I am using Microsoft SQL Server Management Studio 2014

Possible duplicate of [Creating a UDF(User Define Function) if is does not exist and skipping it if it exists](http://stackoverflow.com/questions/8886759/creating-a-udfuser-define-function-if-is-does-not-exist-and-skipping-it-if-it) — Tab Alleman, Jan 25 '16 at 14:03

score 8 · Answer 1 · answered Jan 25 '16 at 14:17

8

CREATE statements, whether they are for TYPE, PROCEDURE, FUNCTION, ... should always be the first statement in a batch.

To work around this in batches like yours execute the CREATE statement using sp_executesql like this:

EXEC sp_executesql N'
    -- your CREATE statement here
';

answered Jan 25 '16 at 14:17

TT.

15,774
6
47
88

Thanks.. Works beautifully for views and functions in my case – eagle275 May 10 '23 at 16:00

score 2 · Accepted Answer · answered Oct 14 '16 at 22:49

If you are trying to do this in regular window within MS SQL Server Management Studio 2014 (or any prior version), where you would normally write all other queries, then your definition of function must be very first statement that is not commented.

See image below. If statement USE PTCRMstaging_test; was not commented, SQL Server Management Studio would give that same error.

Error:create function must be the only statement in the batch

2 Answers2