CODEWITHSUNDEEP
sqlt-sql
cantcatchme
cantcatchme

Reputation: 21

IF ELSE syntax in T-SQL

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.

Upvotes: 2

Views: 324

Answers (2)

Munir
Munir

Reputation: 3612

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

Upvotes: 4

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

Related Questions