Microsoft® SQL Server® 2012 Bible

401

Chapter 16: Programming with T-SQL

16

PKColumn int PRIMARY KEY,

ColumnA int);

CREATE TABLE dbo.FKTable(

FKColumn int PRIMARY KEY REFERENCES dbo.MainTable(PKColumn),

ColumnB int);

CREATE INDEX MainTableIndex ON dbo.MainTable(PKColumn);

--This statement will fail

IF EXISTS (SELECT OBJECT_ID('dbo.MainTable') FROM sys.objects)

BEGIN

DROP TABLE dbo.MainTable;

END;

--This statement will succeed

IF EXISTS (SELECT OBJECT_ID('dbo.MainTable') FROM sys.objects)

BEGIN

IF EXISTS (SELECT OBJECT_ID('dbo.FKTable') from sys.objects)

BEGIN

DROP TABLE dbo.FKTable;

END

IF EXISTS (SELECT OBJECT_ID('dbo.MainTableIndex') FROM sys.objects)

BEGIN

DROP INDEX MainTableIndex

ON dbo.MainTable;

END

DROP TABLE dbo.MainTable;

END;

Using If/Else to Execute an Alternative Statement

The optional ELSE command defi nes code that is executed only when the IF condition is

false. Like IF, ELSE controls only the next single command or BEGIN/END block:

IF Condition

Single line or begin/end block of code;

ELSE

Single line or begin/end block of code;

Looping with WHILE

The WHILE command is used to loop through code while a condition is still true. Just like

the IF command, the WHILE command determines the execution of only the following

single T-SQL command. To control a full block of commands, you should use BEGIN/END.

Some looping methods differ in the timing of the conditional test. The T-SQL WHILE works

in the following order:

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

http://www.it-ebooks.info