SQL Misinterpretation of Return in IF Scope Context-CodePudding

I am trying to script a new database and have a valid scaler function script for one operation. Being a good script writer, I want to check to see if the function exists before attempting to do the Create Function, but I get this error when I combine the two:

Incorrect syntax near the keyword 'FUNCTION'. ... A RETURN statement with a return value cannot be used in this context.

How can both be accomplished?

IF NOT EXISTS (select 1 from INFORMATION_SCHEMA.ROUTINES
    where Specific_schema = 'util'
    AND   specific_name   = 'TableState_Boolean'
    AND   ROUTINE_TYPE    = 'FUNCTION')
BEGIN
    create FUNCTION util.TableState_Boolean()
    RETURNS tinyint
    as BEGIN return 1; END    
end

CodePudding user response：

I would use CREATE OR ALTER to create the Funcation in sql-server

CREATE OR ALTER FUNCTION util.TableState_Boolean()
RETURNS tinyint
AS BEGIN 
    return 1; 
END;

CodePudding user response：

CREATE FUNCTION must be the first statement in a query batch, so use dynamic SQL, eg

IF NOT EXISTS (select 1 from INFORMATION_SCHEMA.ROUTINES
    where Specific_schema = 'util'
    AND   specific_name   = 'TableState_Boolean'
    AND   ROUTINE_TYPE    = 'FUNCTION')
BEGIN
    exec('
    create FUNCTION util.TableState_Boolean()
    RETURNS tinyint
    as BEGIN return 1; END    
    ')
end

CodePudding user response：

Try in two steps like first drop existsing UDF and then create a new one like this:

IF OBJECT_ID('util.TableState_Boolean') IS NOT NULL DROP FUNCTION util.TableState_Boolean
GO

CREATE FUNCTION util.TableState_Boolean ...
GO

. . . or the other way around -- first create an empty "stub" UDF if it does not exist and then ALTER it with its new definition although in this case you'll have to use dynamic SQL too.

IF OBJECT_ID('util.TableState_Boolean') IS NULL EXEC('CREATE FUNCTION dbo.TableState_Boolean() RETURNS tinyint AS BEGIN RETURN NULL END'
GO

ALTER FUNCTION util.TableState_Boolean ...
GO

Btw, CREATE OR ALTER is introduced in SQL 2016 and is not available in previous versions.