Create new function by code if it doesn't exist I want to create new function by script in my database.
The script code is below:

IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return;

CREATE FUNCTION fn_myfunc ()
returns varchar(10)
AS Begin
...
End


But when I execute the above script, SQL Server returns an error: 

'CREATE FUNCTION' must be the first statement in a query batch.
Update Jan 2017 - SQL Server 2016+ / Azure SQL Database

SQL Server 2016 and the current version of Azure SQL Database now has the following syntax for functions, procedures, tables, databases, etc. (DROP IF EXISTS):

DROP FUNCTION IF EXISTS dbo.fn_myfunc;


And SQL Server 2016 Service Pack 1 adds even better functionality for modules (functions, procedures, triggers, views) which means no losing of permissions or dependencies (CREATE OR ALTER):

CREATE OR ALTER FUNCTION dbo.fn_myfunc ...


Both of these syntax enhancements can lead to much simpler scripts used for source control, deployments, etc.

But, if you're using...



Older versions

You need to do what SQL Server does when you script this from Management Studio:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fn_myfunc')
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE FUNCTION ...';
    EXEC sp_executesql @sql;
END


Or you can say:

BEGIN TRY
    DROP FUNCTION dbo.fn_myfunc;
END TRY
BEGIN CATCH
    PRINT 'Function did not exist.';
END CATCH
GO
CREATE FUNCTION...


Or you can just say:

DROP FUNCTION dbo.fn_myfunc;
GO
CREATE FUNCTION...


(Here you will get an error message if the function doesn't already exist, but the script will continue from the next GO, so whether the drop worked or not, the function will still be (re-)created.)

Note that if you drop the function and re-create it, you will lose permissions and potentially dependency information as well.