Microsoft® SQL Server® 2012 Bible

403

Chapter 16: Programming with T-SQL

16

of the batch or procedure. Although this is syntactically correct, and you’re likely to see it

quite a bit in legacy code, GOTO should be avoided if possible. It’s a brute force way to con-

trol query fl ow, and most likely, there is a more elegant way to move the script along.

The label is created by placing a colon after the label name:

LabelName:

The following code sample uses the GOTO command to branch to the ErrorHandler:

label, bypassing the 'more code':

GOTO ErrorHandler;

Print 'more code';

ErrorHandler:

Print 'Logging the error';

Result:

Logging the error

Examining SQL Server with Code

One of the benefi ts of using SQL Server is the cool interface it offers to develop and

administer the database. Management Studio is great for graphically exploring a data-

base; T-SQL code, although more complex, exposes even more detail within a programmer’s

environment.

Dynamic Management Objects

Introduced in SQL Server 2005, dynamic management objects (DMOs) offer a powerful view

into the structure of SQL Server and the databases, as well as the current SQL Server status

(memory, IO, and so on).

As an example of using DMOs, the next query looks at three DMOs concerning objects and

primary keys:

USE AdventureWorks2012;

GO

SELECT s.NAME + '.' + o2.NAME AS 'Table', pk.NAME AS 'Primary Key'

FROM sys.key_constraints AS pk

JOIN sys.objects AS o

ON pk.OBJECT_ID = o.OBJECT_ID

JOIN sys.objects AS o2

ON o.parent_object_id = o2.OBJECT_ID

JOIN sys.schemas AS s

ON o2.schema_id = s.schema_id;

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

http://www.it-ebooks.info