CREATE FUNCTION dbo.abz(@number1 INT)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
IF @number1 < 0
BEGIN
RETURN -1 * @number1
END
IF @number1 = 0
BEGIN --line21
RETURN 0
END
IF @number1 > 0
BEGIN
RETURN @number1
END
END
I get this error when this code is run:
Msg 156, Level 15, State 1, Server DB, Procedure abz, Line 21 Incorrect syntax near the keyword 'IF'.
Why? There's not even an IF statement on that line.
SQL Server functions can have only one RETURN statement and it should be the last statement of the function. You can do that using a dummy variable. Also, line numbers are counted from the last GO statement or the start of script, including blank lines. So, Line 21 as per SQL Server might not be line 21 in your editor.
CREATE FUNCTION dbo.abz(@number1 INT)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @ret INT
IF @number1 < 0
BEGIN
SET @ret = -1 * @number1
END
IF @number1 = 0
BEGIN --line21
SET @ret = 0
END
IF @number1 > 0
BEGIN
SET @ret = @number1
END
RETURN @ret
END
You should be using ABS() instead of this function. It does the exact same thing. However, to show you what you need to do for functions going forward to not get an error, I will present the rewritten function to remove the error:
CREATE FUNCTION dbo.abz(@number1 INT)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
IF @number1 < 0
BEGIN
set @number1 = -1 * @number1
END
Return @number1
END
Note I removed the last two if statements as they return the same value as the input variable. But the real reason your function is failing is that the very last statement of the the function must be the return, you can't use return within the if statements.
abs()function, which does the same thing.The last statement included within a function must be a return statement, which makes sense since not all your paths of execution return something.