sqlsql-serversql-server-2008t-sqlsql-function

Msg 178, Level 15, State 1, Line 8 A RETURN statement with a return value cannot be used in this context


I received this error message while trying to execute below SQL.

Error:

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'FUNCTION'. Msg 178, Level 15, State 1, Line 8 A RETURN statement with a return value cannot be used in this context.

SQL:

IF (NOT EXISTS (SELECT * FROM DBO.TRACKING WHERE CR = 123)) 

BEGIN

  CREATE FUNCTION [dbo].[UDFDate]()
  RETURNS DATETIME
  AS BEGIN
     RETURN CAST('9999-12-31' AS DATETIME)
  END
END

Am I not allowed to create a Function inside if statement ?


Solution

  • You cannot create a function inside an if statment this way you will get the following warning

    Incorrect syntax: CREATE FUNCTION must be the only statment in the batch

    enter image description here

    you can do it by creating a variable stor the create query in this variable and execute it:

    IF (NOT EXISTS (SELECT * FROM DBO.TRACKING WHERE CR = 123)) 
    
    BEGIN
    
     DECLARE @strQuery AS VARCHAR(MAX)
    
     SET @strQuery = '
    
      CREATE FUNCTION [dbo].[UDFDate]()
      RETURNS DATETIME
      AS BEGIN
         RETURN CAST(''9999-12-31'' AS DATETIME)
      END
    '
    
    EXEC(@strQuery)
    
    END
    

    enter image description here

    but i didn't understand why creating a function to return a static value??!!