If function not exist then create function in SQL server

Question

 If not Exists (Select * from sys.objects where name ='FNLX_getDate' and type =N'FN')
 Create function [dbo].[FNLX_getDate]() returns Datetime
 as
 Begin
 .................
 End

When I execute the above script I get the error

Incorrect syntax near the keyword 'function'.

What can be wrong?

Using Microsoft SQL Server to execute above script.

Answer 1

If you are running SQL Server 2016 SP1+, you can use CREATE OR ALTER to avoid conditionally dropping the function beforehand:

CREATE OR ALTER FUNCTION [dbo].[FNLX_getDate]() returns Datetime
AS
BEGIN
...
END;

Answer 2

Unfortunately you can't use create function like that. It must be the first statement in a batch.

You can try dynamic SQL instead like:

If not Exists (Select * from sys.objects where name ='FNLX_getDate' and type =N'FN')
 BEGIN
 DECLARE @sql NVARCHAR(MAX);
 SET @sql = N'CREATE FUNCTION ...';
 EXEC sp_executesql @sql;
 END

Or the classic way (like you can see if you generate scripts with SSMS) : drop and create:

If Exists (Select * from sys.objects where name ='FNLX_getDate' and type =N'FN')
 drop function [dbo].[FNLX_getDate]
GO
Create function [dbo].[FNLX_getDate]() returns Datetime
as
Begin
.................
End

Notice the GO SSMS batch separator.

• Nikhil D: i used dynamic sql but in function i have line Return cast('Timezone conversion: Database timezone should be same as Application timezone or UTC in LU_TimeZoneConfig Table ' as int); when i execute its showing error Msg 102, Level 15, State 1, Line 29 Incorrect syntax near 'Timezone '.
• Elaskanator: @NikhilD You need to have two single quotation marks when building dynamic SQL. For example EXEC('SELECT [MyStringValue] = ''asdf''') Note there are two ' not one " before asdf and three of them after.