0
    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.

closed as off-topic by Paul White Dec 20 '16 at 11:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

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;
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.

  • 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 '. – Nikhil D Dec 20 '16 at 9:51
  • @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. – Elaskanator Aug 12 at 14:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.