SQL Server : basic if condition in function

Question

I have problem with basic if condition in function.. I have something like this

IF  LEFT(@text, 1) = '#'
        BEGIN
            /* trim first character if is '#' */
            RETURN RIGHT(@text,LEN(@text)-1)
        END
    ELSE
        BEGIN
            RETURN @text
        END
    END

Console output show me this

Incorrect syntax near the keyword 'IF'. Incorrect syntax near the keyword 'END'.

I wonder where the problem is. Can I simplify the if condition like this?

IF  LEFT(@text, 1) = '#'  RETURN RIGHT(@text,LEN(@text)-1)
ELSE RETURN @text

Thank you

edit: now I have that if in function

CREATE FUNCTION Trimming (@text VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @TrimText AS VARCHAR(255) ;

    SET @TrimText=LTRIM(RTRIM(@text)
    IF  LEFT(@TrimText, 1) = '#'
        BEGIN
            RETURN RIGHT(@TrimText,LEN(@TrimText)-1)
        END
    ELSE
        BEGIN
            RETURN @TrimText
        END
END

and console says

Incorrect syntax near the keyword 'IF'.

there is nothing wrong with this code. i.sstatic.net/TwBAh.png — Royi Namir
– Royi Namir, Commented Jul 9, 2013 at 13:36

Darren · Accepted Answer · 2013-07-09 14:04:24Z

1

You have an additional END statement, your query should look like:

IF  LEFT(@text, 1) = '#'
BEGIN
     /* trim first character if is '#' */
      RETURN RIGHT(@text,LEN(@text)-1)
END
ELSE
BEGIN
      RETURN @text
END

http://sqlfiddle.com/#!6/270ca/4

From your edit

SET @TrimText=LTRIM(RTRIM(@text)

You are missing a )

SET @TrimText=LTRIM(RTRIM(@text))

edited Jul 9, 2013 at 14:04

answered Jul 9, 2013 at 13:39

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Muflix Over a year ago

Thank you it helped.. i rewrite it to this pastebin.com/mpJ9cd6E but now im getting Incorrect syntax near the keyword 'IF'.

Darren Over a year ago

@Muflix - pastebin is blocked on my network. Can you include it in a SQLFiddle or modify your question to include the code?

Muflix Over a year ago

Thank you, now it works i only have to add RETURN before last END because Console says last statement must be return.. but it is necessary if i have IF/ELSE there ? (okay i just deleted ELSE section now its perfect :-)

ssarabando · Accepted Answer · 2013-07-09 13:38:15Z

1

The last END is not necessary since an IF does not require an END. Also, since you only have one statement inside the IF and another one inside the ELSE, you don't need to write BEGIN and END.

answered Jul 9, 2013 at 13:38

ssarabando

3,5272 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL Server : basic if condition in function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related