"IF ... ELSE IF ..." Statement Structures in SQL Server

Q

How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL?

✍: FYIcenter.com

A

"IF ... ELSE IF ..." statement structure is used to select one of the specified statements to be executed based on specified Boolean conditions. Here is the syntax of "IF ... ELSE IF ..." statement structure:

IF condition_1 statement_1;
ELSE IF condition_2 statement_2;
...
ELSE IF condition_n statement_n;
ELSE statement_o;

IF condition_1
   -- statement_block_1;
   BEGIN
      ... statements ...
   END
ELSE IF condition_2
   -- statement_block_2;
   BEGIN
      ... statements ...
   END
...
ELSE IF condition_n
   -- statement_block_n;
   BEGIN
      ... statements ...
   END
ELSE 
   -- statement_block_o;
   BEGIN
      ... statements ...
   END

When a IF...ELSE IF ... statement is executed, the system will:

  • Evaluate the "condition_1" execute statement_1 or statement_block_1, if condition is true.
  • Evaluate the "condition_2" execute statement_2 or statement_block_2, if condition is true.
  • And so on.
  • Execute statement_o or statement_block_o, if none of the conditions is true.

The tutorial exercise below shows you how to use an IF ... ELSE IF ... statement structure to selectively execute one of the CREATE TABLE statements:

DECLARE @site_name VARCHAR(40);
SET @site_name = 'SQA';
IF @site_name = 'DBA' 
   CREATE TABLE dba_links (url VARCHAR(256));
ELSE IF @site_name = 'SQA' 
   CREATE TABLE sqa_links (url VARCHAR(256));
ELSE
   PRINT 'Unknown site name: '+@site_name;
GO
Command(s) completed successfully.

SELECT name FROM sys.tables WHERE name LIKE '%links';
GO
sqa_links

Here is another example of IF ... ELSE IF ... statement using statement blocks:

DECLARE @site_name VARCHAR(40);
SET @site_name = 'SQA';
IF @site_name = 'DBA' 
   BEGIN
      PRINT 'Dropping table: dba_links';
      DROP TABLE dba_links;
   END
ELSE IF @site_name = 'SQA' 
   BEGIN
      PRINT 'Dropping table: sqa_links';
      DROP TABLE sqa_links;
   END
ELSE
   PRINT 'Unknown site name: '+@site_name;
GO
Dropping table: sqa_links

 

Conditional Statements and Loops in SQL Server in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-01-11, 259👍, 0💬