Create new function by code if it doesn't exist

Question

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.

Answer 1

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.

• zinking: so there is no keyword $$CREATE OR REPLACE$$ ? pity.
• Aaron Bertrand: @zinking no, but maybe in a future version. Please vote/comment: connect.microsoft.com/SQLServer/feedback/details/127219/…
• bluish: @AaronBertrand Good idea to vote for it, but link is broken
• Aaron Bertrand: @bluish yeah sorry, they removed the item sometime between three years ago when I posted the link and now... Try connect.microsoft.com/SQLServer/feedback/details/344991/… or connect.microsoft.com/SQLServer/feedback/details/351217/…
• Bruno: This might be available now: blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/…

Answer 2

You have the option of check if the object exists in the database and create if not:

IF OBJECT_ID('new_function', 'FN') IS NULL
BEGIN
 EXEC('CREATE FUNCTION new_function() RETURNS INT AS BEGIN RETURN 1 END');
END;
go
ALTER FUNCTION new_function() RETURNS INT AS
BEGIN
...

Answer 3

The error is pretty self-explanatory. There are a couple ways of fixing it.

1. Separate the script into different batches in Management Studio using the GO pseudo-keyword, and DROP/CREATE the object. (Note that the keyword itself can be changed in Management Studio options, but this is the de facto setting, so I suggest leaving it alone).

   When you run a script (or the selected portion of a script), Management Studio separates each chunk of script between GOs, and sequentially sends the parts to SQL Server as separate batches.

2. Use dynamic SQL to send a separate batch from within another batch.

   This is the preferred method, because then your script does not depend on external functionality to execute correctly. For example, if your application has a database update program, generally speaking it will load a script file and then execute it on the target server. Either you will have to add logic to separate the batches as Management Studio does (note: fraught with peril), or write the script in a way that the entire script can be executed successfully as a single batch.

   As mentioned in another answer, you can do a test/CREATE using this method (or some other combination of DROP/CREATE, etc.). What I prefer to do is create a stub object if the object doesn't exist, and then use ALTER <object> to actually do the creation or alteration. This approach doesn't drop dependencies, such as permissions or extended properties, and it isn't necessary to copy/paste error-prone logic to do the CREATE/ALTER in a single statement.

   Here is the template I use for creating or changing a scalar function. I'll leave it as an exercise for the reader to adapt this to other types of objects (stored procs, triggers, etc.).

IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<schema>].[<function name>]') AND type IN ('FN', 'FS'))
 EXEC sp_executesql N'CREATE FUNCTION [<schema name>].[<function name>] (@a int) RETURNS int AS BEGIN /* Stub */ RETURN @a END'
EXEC sp_executesql N'
ALTER FUNCTION [<schema name>].[<function name>]
/* ... */
'

• Aaron Bertrand: How can option 1 work here? Adding a GO actually ensures the script will break, since the IF will lead nowhere.
• Jon Seigel: @Aaron: I was thinking of the DROP/CREATE scenario -- edited. Thanks.