CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-function
user3369060
user3369060

Reputation: 81

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 ?

Upvotes: 1

Views: 2686

Answers (2)

leobelones
leobelones

Reputation: 598

There are no reasons why you would want to create functions inside a stored procedure during runtime. Functions in SQL SERVER work similarly to stored procedures, so if you want to create a function you need to do it outside of the context and scope of a stored procedure and it will be saved on your database to be accessed at any time.

Ontopic, if you only want to exhibit that date value you could just remove the function creation and select the datetime result as this:

IF (NOT EXISTS (SELECT TOP 1 1 FROM DBO.TRACKING WHERE CR = 123)) 
BEGIN    
  SELECT CAST('9999-12-31' AS DATETIME)
END

PS: Don't use "SELECT *" when you just want to know if some value exists. It creates unnecessary overhead to bring all columns when you just want to know if anything exists.

Upvotes: 2

Hadi
Hadi

Reputation: 37358

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??!!

Upvotes: 0

Related Questions