SQL Server 2000 Stored Procedure Programming

Cover......Page 1
AT A GLANCE......Page 6
CONTENTS......Page 8
ACKNOWLEDGMENTS......Page 16
CHAPTER 1 Introduction......Page 17
WHAT YOU WILL FIND IN THIS BOOK......Page 20
REQUIREMENTS......Page 22
NEW FEATURES IN SQL SERVER 2000......Page 23
Improved Scalability and Reliability......Page 24
Improved Development and Administration Environment......Page 25
Sample Database Installation......Page 26
Purpose and Design of the Sample Database......Page 28
Database Diagram......Page 29
SUMMARY......Page 37
CHAPTER 2 Relational Database
Concepts and the
SQL Server
Environment......Page 38
Database Objects......Page 40
SQL SERVER 2000 TOOLS......Page 49
Service Manager......Page 50
Query Analyzer......Page 51
Enterprise Manager......Page 53
DTS-Import and Export Data......Page 56
osql and isql......Page 57
Client Network Utility......Page 58
The Help Subsystem and SQL Server Books Online......Page 60
Execution of Stored Procedures from Query Analyzer......Page 61
Managing Stored Procedures from Enterprise Manager......Page 66
Editing Stored Procedures in Query Analyzer......Page 71
Syntax Errors......Page 74
The Create Stored Procedure Wizard......Page 76
SUMMARY......Page 80
EXERCISES......Page 81
CHAPTER 3 Stored Procedure
Design Concepts......Page 82
Composition......Page 83
Functionality......Page 87
Syntax......Page 95
TYPES OF STORED PROCEDURES......Page 96
The Compilation and Execution Process......Page 98
Reuse of Execution Plans......Page 99
Recompiling Stored Procedures......Page 103
Storing Stored Procedures......Page 105
Listing Stored Procedures......Page 107
Viewing Stored Procedures......Page 110
Renaming Stored Procedures......Page 112
Listing Dependent and Depending Objects......Page 113
Consistent Implementation of Complex Business Rules and Constraints......Page 116
Maintainability......Page 117
Enforcement of Security......Page 118
SUMMARY......Page 119
EXERCISES......Page 120
CHAPTER 4 Basic Transact-SQL
Programming
Constructs......Page 122
TSQL IDENTIFIERS......Page 123
DATABASE OBJECT QUALIFIERS......Page 127
Groups of Datatypes......Page 128
User-Defined Datatypes......Page 140
VARIABLES......Page 141
Local Variables......Page 142
Global Variables......Page 146
TableVariables......Page 150
FLOW CONTROL STATEMENTS......Page 151
Comments......Page 152
Statement Blocks-Begin...End......Page 156
Conditional Execution-the If Statement......Page 157
Looping-the While Statement......Page 162
Unconditional Execution-the GoTo Statement......Page 165
Scheduled Execution-the WaitFor Statement......Page 169
CURSORS......Page 170
Transact-SQL Cursors......Page 171
Cursor-Related Statements and Functions......Page 175
Problems with Cursors......Page 177
The Justified Uses of Cursors......Page 178
SUMMARY......Page 180
EXERCISES......Page 181
CHAPTER 5 Functions......Page 184
In Selection and Assignment......Page 186
In Filtering Criteria......Page 187
Instead of Tables......Page 188
Scalar Functions......Page 190
Aggregate Functions......Page 234
Rowset Functions......Page 236
SUMMARY......Page 238
EXERCISES......Page 239
CHAPTER 6 Composite
Transact-SQL
Constructs—Batches,
Scripts, and
Transactions......Page 241
BATCHES......Page 243
Batches and Errors......Page 245
DDL Batches......Page 249
Database Scripting......Page 254
THE TRANSACTION CONCEPT......Page 256
Autocommit Transactions......Page 257
Explicit Transactions......Page 259
Transaction Processing Architecture......Page 261
Nested Transactions......Page 263
Named Transactions......Page 269
Savepoints......Page 270
Locking......Page 273
Distributed Transactions......Page 277
Typical Errors......Page 280
SUMMARY......Page 284
EXERCISES......Page 285
CHAPTER 7 Debugging and Error Handling......Page 287
What Is a "Bug"?......Page 288
The Debugging Process......Page 289
Debugging Tools and Techniques......Page 292
Typical Errors......Page 310
Why Bother?......Page 316
Tactics of Error Handling......Page 318
A Coherent Error Handling Methodology......Page 322
XACT_ABORT......Page 328
Raiserror......Page 330
SUMMARY......Page 331
EXERCISES......Page 332
CHAPTER 8 Developing
Professional Habits......Page 335
THE CONCEPT OF SOURCE CODE CONTROL......Page 336
Introduction to Microsoft Visual SourceSafe......Page 337
Administering the Visual SourceSafe Database......Page 338
Adding a Database to Visual SourceSafe......Page 340
Managing Stored Procedures......Page 342
Visual SourceSafe Explorer......Page 346
Why Bother?......Page 352
Naming Objects and Variables......Page 353
Suggested Convention......Page 356
SUMMARY......Page 361
CHAPTER 9 Special Types of
Procedures......Page 363
System Stored Procedures......Page 364
Extended Stored Procedures......Page 366
Temporary Stored Procedures......Page 375
Remote Stored Procedures......Page 376
Design of User-Defined Functions......Page 377
Table-Valued User-Defined Functions......Page 382
In-Line Table-Valued User-Defined Functions......Page 386
Managing User-defined Functions in Enterprise Manager......Page 389
TRIGGERS......Page 390
Physical Design......Page 392
Handling Changes on Multiple Records......Page 400
Nested and Recursive Triggers......Page 402
Trigger Restrictions......Page 403
Triggers in SQL Server 2000......Page 404
Managing Triggers......Page 409
Trigger Design Recommendations......Page 413
Transaction Management in Triggers......Page 416
Using Triggers......Page 417
SUMMARY......Page 424
EXERCISES......Page 426
CHAPTER 10 Advanced Stored
Procedure
Programming......Page 427
Executing a String......Page 429
Query By Form......Page 431
Using the sp_executesql Stored Procedure......Page 434
OPTIMISTIC LOCKING USING TIMESTAMPS......Page 436
Timestamp......Page 437
TSEQUAL Function......Page 439
Timestamp Conversion......Page 441
FULL TEXT SEARCH AND INDEXES......Page 444
NESTED STORED PROCEDURES......Page 446
Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure......Page 452
Using a Cursor to Pass a Recordset to a Nested Stored Procedure......Page 456
HOW TO PROCESS THE RESULTSET OF A STORED PROCEDURE......Page 459
A Standard Problem and Solution......Page 466
Identity Values and Triggers......Page 468
Sequence Number Table......Page 469
Preserving an Identity Value in a Temporary Table......Page 472
GUIDS......Page 475
A WHILE LOOP WITH MIN OR MAX FUNCTIONS......Page 476
PROPERTY MANAGEMENT......Page 479
SUMMARY......Page 480
EXERCISES......Page 482
CHAPTER 11 Interaction with
the SQL Server
Environment......Page 484
EXECUTION OF OLE AUTOMATION OBJECTS......Page 485
sp_OAMethod......Page 489
sp_OAGetProperty......Page 490
Datatype Conversion......Page 491
RUNNING PROGRAMS......Page 492
Registry Subtrees......Page 494
Keys and Subkeys......Page 495
THE REGISTRY AND SQL SERVER......Page 496
xp_regwrite......Page 498
JOBS......Page 499
Administration of Jobs......Page 500
Alternative to Job Scheduler......Page 505
Stored Procedures for Maintaining Jobs......Page 506
Operators and Alerts......Page 507
SQL SERVER AND THE WEB......Page 508
Web Assistant......Page 509
Web Task Stored Procedures......Page 513
sp_makewebtask......Page 514
sp_dropwebtask......Page 515
Web Page Templates......Page 516
xp_sprintf......Page 518
MAIL......Page 519
xp_sendmail......Page 520
xp_readmail......Page 522
sp_processmail......Page 523
A Long Time Ago, in an Environment Not So Far Away…......Page 524
Now......Page 525
Security Architecture......Page 527
Implementing Security......Page 531
Synchronization of Login and User Names......Page 538
Managing Application Security Using Stored Procedures, User-Defined Functions, and Views......Page 539
Managing Application Security Using a Proxy User......Page 542
Managing Application Security Using Application Roles......Page 543
SUMMARY......Page 544
EXERCISES......Page 545
CHAPTER 12 XML Support in SQL
Server 2000......Page 546
XML (R)EVOLUTION......Page 547
Introduction to Markup Languages......Page 549
Building Blocks of Markup Languages......Page 550
XML......Page 551
XML Document Quality......Page 557
XML Parsers and DOM......Page 564
Linking and Querying in XML......Page 565
Transforming XML......Page 571
WHY XML?......Page 573
Exchange of Information Between Organizations......Page 574
Transact-SQL Language Extensions......Page 576
OPENXML......Page 599
Publishing Database Information Using XML......Page 609
SUMMARY......Page 648
EXERCISES......Page 649
APPENDIX A T-SQL and XML
Datatypes in
SQL Server 2000......Page 652
APPENDIX B Solutions to the Exercises......Page 660
Index......Page 735