Microsoft® SQL Server® 2012 Bible

400

Part IV: Programming with T-SQL

IF Condition

Begin;

Multiple lines;

End;

Using If exists() as an Existence-Based Condition

While the IF command may seem limited, the condition clause can include several powerful

SQL features similar to a WHERE clause, such as IF EXISTS() and IF.. .IN().

The IF EXISTS() structure uses the presence of any rows returned from a SQL SELECT

statement as a condition. Because it looks for any row, the SELECT statement should select

all columns (*). This method is faster than checking an @@rowcount >0 condition because

the total number of rows isn’t required. As soon as a single row satisfi es the IF EXISTS(),

the query can move on.

The following example script uses the IF EXISTS() technique to process orders only if

any open orders exist:

USE AdventureWorks2012;

IF EXISTS

(

SELECT * FROM Production.ProductInventory WHERE Quantity = 0

)

BEGIN;

PRINT 'Replenish Inventory';

END;

There is effectively no difference between SELECT * or selecting a column. However,

selecting all columns enables SQL Server to select the best column from an index and

might, in some situations, be slightly faster.

Using Nested If for Complex Query Flow

More complex query fl ow can be achieved by nesting IF statements. You can do this when

dropping tables in a database setup script; checking fi rst for the existence of the table

and then nesting IF statements below that to check for and drop dependent objects like

indexes and foreign keys before dropping the table you were after in the fi rst place.

The following example creates two tables, one with a foreign key relationship to the other

and a single index on the main table. In the fi rst IF statement, you attempt to drop dbo

.MainTable. This fails because the object has dependencies that need to be handled fi rst.

In the second IF, you nest IF statements to fi rst check for the existence of dbo

.MainTable, and if it does exist, drop the dependent objects prior to issuing the DROP

TABLE dbo.MainTable command.

USE AdventureWorks2012;

GO

CREATE TABLE dbo.MainTable(

c16.indd 400c16.indd 400 7/30/2012 5:38:08 PM7/30/2012 5:38:08 PM

http://www.it-ebooks.info