SQL Server 2000 : stored procedure programming

"Tired of pubs and Northwind? Tired of books that just lie there? This book will engage you interactively with its sample Asset tracking database and end-of-chapter exercises. You'll learn by doing. " —Karen Watterson,Editor,Pinnacle Publishing's SQL Server Professional "Finally! This book dedicated to T-SQL Stored Procedure development is long overdue. . . a must have for the developer who is serious about creating powerful and scalable SQL Server 2000 architectures. " —Ed Musters,Chief Technology Officer,Sage Information Consultants,Inc. Design,deploy,and manage stored procedures in enterprise data applications SQL Server 2000 Stored Procedure Programming shows you how to use Transact-SQL to design,debug,and manage custom stored procedures,functions,and triggers. You'll learn to manage SQL Server resources using new features of Enterprise Manager,Query analyzer,Visual Basic,and Visual Studio,implement robust error handling,and establish the professional habits you need to become an expert SQL Server developer. This guide contains simple explanations for developers who are just starting to work with stored procedures and advanced recommendations and techniques for experienced DBAs. SQL Server stored procedures and advanced recommendations and techniques for experienced DBAs. SQL Server experts provide concise explanations,design recommendations,many examples,and a real-world case study. Design user-defined,system,temporary,global temporary,extended,remote,and nested stored procedures Adopt tips and tricks using identity,timestamp,sql_variant,uniqueidentifier,optimistic locking,dynamically constructed queries,temporary tables,andcursors Write code to interact with COM,mai,executables,the Registry,jobs,and security Manage application security Locate and fix semantic errors using TSQL Debugger in Query Analyzer,Visual Basic,or Visual Studio,and using manual debugging techniques Create user-defined,table-valued,and in-line functions Design instead-of triggers and after triggers Manage transactions and locks Develop dynamic,Web-based applications using SQL Server's XML capabilities 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 This book is an A-Z guide to building and using stored procedures in SQL Server applications. With this book, developers can custom design stored procedures to write high-performance SQL Server applications that effectively can be maintained and scaled. They also will learn to administer SQL Server environments using Microsoft's system stored procedures. 20 line illustrations.