If and While Statement in SQL Server 2012

In this article, we will see how to use If and While statements in SQL Server. If you have programmed in languages like C# or other languages then you are probably familiar with If and While statements. Transact-SQL also gives you this option to repeat the expression using a While loop and check the condition with an If loop. So let's take a look at a practical example of If and While statements with break and continue into SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple steps to do that are described here.

While Statement

 If the expression is true, the statement is executed, and then the expression is evaluated again to determine if the statement should be executed again. This process repeats until the expression evaluates to false.

Syntax

The While loop syntax is as defined below:

WHILE bool_exp
{sql_statement}
[BREAK]
{sql_statement}
[CONTINUE]

In the preceding syntax if the bool_exp value is true then sql_statement will be executed. This process repeats until the expression evaluates to false.

Example

-- While statement

print 'While statement'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

END

The execution of statements in the WHILE loop can be controlled from inside the loop with the break and continue keywords.

BREAK  Keyword

Break forces a loop to exit immediately. Suppose we repeat a number from 1 to 20. The loop will go through all the numbers. We want to exit when we get to the number 11 in the loop. That can be done simply by using the break keyword with a while loop.

Example

-- While Statement with Beak

print 'While Statement with break'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

if(@countnumber=22)

break

END

 

Continue Keyword

 

This does the opposite of break. Instead of terminating the loop, it immediately loops again, skipping the rest of the code. The continue statement skips the value and jumps to the while loop without terminating the loop.

Example

-- While Statement with continue

print 'While Statement with continue'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

CONTINUE;

if(@countnumber=4)  -- This will never execute.

break

END

Output of the preceding examples in SQL Server Management Studio

Now run the preceding examples in SQL Server Management Studio.

-- While statement

print 'While statement'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

END

 go

 

-- While Statement with Beak

print 'While Statement with break'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

if(@countnumber=22)

break

END

go

-- While Statement with continue

print 'While Statement with continue'

 

DECLARE @countnumber varchar(50)

SET @countnumber  = 1

WHILE (@countnumber<=30)

BEGIN

PRINT 'Number=' +  @countnumber

SET @countnumber = @countnumber  + 3

CONTINUE;

if(@countnumber=4)  -- This will never execute.

break

END

Output

IF Statement

IF a Boolean expression which follows the keyword IF, evaluates to true then ____. If the IF statement contains an else statement, then a second group of SQL statements can be executed if the IF condition evaluates to false. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax

IF Bool_exp
{ sql_statement  }
[ ELSE
{ sql_statement } ] 

Creating table in SQL Server

createtable UsingIF

(

priceint,

Typevarchar(20),

Titlevarchar(100)

);

go

insertinto UsingIFvalues('20','cold','rasnel hussy this is the title')

go

select* fromUsingIF;

 

Output
 

 

Example

 

IF (SELECTAVG(price)FROM UsingIF WHEREtype ='cold')<= 15

BEGIN

  PRINT 'The following titles are excellent cold books:'

  PRINT ' '

  SELECT SUBSTRING(title, 1, 35)AS Title

  FROM UsingIF

  WHERE type= 'cold'

END

ELSE

  PRINT 'Average price is more than 15.'

Output