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 ?
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
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
but i didn't understand why creating a function to return a static value??!!