Conditions IF ELSE in Transact SQL

In this post I will write about SQL conditions. It seems to me that construction “IF … ELSE” you can find in any language. Transact SQL is not exclusion.

Let’s see IF … ELSE in action on the sample below.

IF @s = 'abcdef'
   BEGIN 
     select 'yes'
   END
ELSE IF @s = '123'
   BEGIN
	 select 'no'
   END
ELSE
   BEGIN
	 select '0'
   END

If you execute this code, you’ll get errors that @s is not declared.

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable “@s”.

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword ‘ELSE’.

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable “@s”.

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword ‘ELSE’.

You need to declare variable @s and set it’s value.  You need to add above this code a string like ‘DECLARE @s CHAR(6) = ‘abcdefg’ ‘

Let’s see 3 different @s values.

 

1. Let’s set @s = ‘abcdefg’, but it’s type is CHAR(6)

DECLARE @s CHAR(6) = 'abcdefg'

In this case if we execute SQL code, we’ll get the result “yes” though ‘abcdefg’ is not equal to ‘abcdef’. It’s because length of our variable is equal to 6 and our characters are truncated after 6th symbol.

If we look at the @s value, we can see it’s real value (Figure 2):

DECLARE @s CHAR(6) = 'abcdefg'
SELECT @s

 

2. On figure 3 you can see the result if we set @s to ‘123’. Then we can see the result of ELSE IF section.

DECLARE @s CHAR(6) = '123'

 

3. If we set @s to, for example, ‘12355’, we can see the result of ELSE section (Figure 4)

DECLARE @s CHAR(6) = '12355'