SQL Server 2000 Stored Procedures Handbook.doc

sql server 2000 stored procedures handbooksql server 2000 stored procedures handbooktable of contents1. table of contents52. backcover53. sql server 2000 stored procedures handbook64. introduction94.1 book outline105. chapter 1: stored procedures overview115.1 what is a stored procedure?115.2 creating stored procedures145.3 why use stored procedures?165.4 replication165.5 designing good stored procedures175.6 data integrity185.7 calling a stored procedure195.8 rules for stored procedures205.9 modifying stored procedures215.10 parameter usage225.11 flow control225.12 nesting in stored procedures285.13 returning values295.14 restoring the example databases to their default condition315.15 summary326. chapter 2: optimizing and debugging326.1 the execution plan356.2 common mistakes426.3 error handling466.4 sql server debugger506.5 summary547. chapter 3: concurrency, cursors, and transactions557.1 cursors557.2 transactions697.3 concurrency777.4 summary928. chapter 4: common practices with stored procedures938.1 typical purposes938.2 points to avoid while using stored procedures1088.3 summary1119. chapter 5: system stored procedures1129.1 system stored procedures1129.2 undocumented functionality1239.3 performing complex tasks with system stored procedures1369.4 tracing object creation and deletion1409.5 sql mail1409.6 summary14710. chapter 6: user defined functions14710.1 udfs14810.2 creating a udf14910.3 scalar valued functions15310.4 table data type15410.5 schema binding15910.6 stored procedures versus udfs16010.7 summary16011. chapter 7: triggers16111.1 what is a trigger?16111.2 why use triggers?16211.3 performance16311.4 creating and using a trigger16411.5 types of trigger16711.6 transactions and triggers17111.7 trigger versus constraints17211.8 triggers versus stored procedure17311.9 replication17311.10 recursion17411.11 good practice17511.12 bad practice17511.13 summary17512. chapter 8: security17612.1 sql server vulnerabilities17612.2 encrypting stored procedures19112.3 summary19613. appendix a: support, errata, and 19613.1 errata19613.2 the peer-to-peer forums at 19613.3 customer support and feedback19614. index19714.1 index_a19814.2 index_b19914.3 index_c20014.4 index_d20414.5 index_e20814.6 index_f21014.7 index_g21114.8 index_h21214.9 index_i21314.10 index_k21414.11 index_l21514.12 index_m21614.13 index_n21614.14 index_o21714.15 index_p21914.16 index_q22114.17 index_r22214.18 index_s22514.19 index_t23814.20 index_u24114.21 index_v24314.22 index_w24414.23 index_x-z24515. list of figures24816. list of examples2482471. table of contentssql server 2000 stored procedures handbookbytony bain, louis davidson, robin dewsonandchuck hawkins isbn:1590592875apress 2003 (288 pages)this handbook shows how to write faster and more robust stored procedures and functions, learn effective, real-world solutions to problems faced by database developers, designers, and administrators, and master topics such as optimizing and debugging.table of contents sql server 2000 stored procedures handbook (see 3.)introduction (see 4.)chapter 1(see 5.)-stored procedures overviewchapter 2(see 6.)-optimizing and debuggingchapter 3(see 7.)-concurrency, cursors, and transactionschapter 4(see 8.)-common practices with stored procedureschapter 5(see 9.)-system stored procedureschapter 6(see 10.)-user defined functionschapter 7(see 11.)-triggerschapter 8(see 12.)-securityappendix a(see 13.)-support, errata, and index (see 14.)list of figures (see 15.)list of examples (see 16.)2. backcoverstored procedures are compiled t-sql statements that reside on the database. they are the cornerstones of successful data manipulation, and data-handling operations of every complexity make use of them. this book is an in-depth guide to a key area of database development.this book tackles real-world problems faced by developers when working with stored procedures, showing you how to solve these problems, avoid the common pitfalls, and produce faster, more robust stored procedures. put simply: if you want to write better stored procedures, then this is the book for you.about the authors tony bain is the founder of tony bain & associates. he has worked with sql server for the last 6 years, and he is passionate about all database technologies, especially when they relate to enterprise availability and scalability. tony currently holds the microsoft certified system engineer, microsoft certified solution developer, and microsoft certified database administrator certifications.louis davidson has been in the it industry for 10 years as a corporate database developer and architect. he is currently a database administrator for compass technology management, supporting the christian broadcasting network and northstar studios in nashville, tennessee.robin dewson has been hooked on programming ever since he bought his first computer in 1980, a sinclair zx80. his first main application of his own was a visual foxpro application that could be used to run a fantasy league system. it was at this point he met up with a great help in his pc development life, jon silver at step one technologies, where in return for training, he helped jon with some other visual foxpro applications. from there, realizing that the marketplace for visual foxpro was limited, he decided to learn visual basic and sql server.chuck hawkins is a senior consultant and database administrator in virginia beach, virginia. he works for compass technology management, where he manages the daily ups and downs of multiple servers for several large ministries. he has extensive experience in t-sql programming, replication, performance tuning, developer management, and routine server management. chuck has spoken at the professional association for sql server conferences in the united states and the uk. he has contributed columns to several magazines and was a contributing author to professional sql server 2000 database design.3. sql server 2000 stored procedures handbooktony bainlouis davidsonrobin dewsonchuck hawkinsapress copyright 2003 tony bain, louis davidson, robin dewson, and chuck hawkinsall rights reserved. no part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher.(pbk):1-59059-287-52345678910trademarked names may appear in this book. rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.distributed to the book trade in the united states by springer-verlag new york, inc., 175 fifth avenue, new york, ny, 10010 and outside the united states by springer-verlag gmbh & co. kg, tiergartenstr. 17, 69112 heidelberg, germany.in the united states: phone 1-800-springer, email , or visit . outside the united states: fax +49 6221 345229, email ordersspringer.de, or visit http:/www.springer.de.for information on translations, please contact apress directly at 2560 ninth street, suite 219, berkeley, ca 94710. phone 510-549-5930, fax 510-549-5939, email , or visit .the information in this book is distributed on an as is basis, without warranty. although every precaution has been taken in the preparation of this work, neither the author(s) nor apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work.the source code for this book is available to readers at in the downloads section. you will need to answer questions pertaining to this book in order to successfully download the code.credits editorial board dan applemancraig berrygary cornelltony davissteven rycroftjulian skinnermartin streicherjim sumserkaren wattersongavin wrayjohn zukowskimanaging editor louay fatoohicommissioning editor douglas patersonindexer andrew criddletechnical editors arun nairveena nairtechnical reviewers mike ahernscott allennatalia bortnikercristian darielouis davidsonmike faulkinburybrian knightjohn boyd nolanmorgan skinnersakhr younessproduction coordinator sarah hallproof reader dev lunsfordcover design kurt kramesproject managers safiulla shakircharlotte smithabout the authors tony bain tony bain is the founder of tony bain & associates and he has worked with sql server for the last 6 last years. tony is passionate about all database technologies especially when they relate to enterprise availability and scalability.tony spends a lot of his time writing and presenting database topics and he currently has microsoft certified system engineer, microsoft certified solution developer, and microsoft certified database administrator certification.thanks to linda, laura, stephanie, and william for their continued support and thanks also to craig walker who is a .net legend! louis davidson louis davidson has been in the technology industry for ten years, as a corporate database developer and architect. currently, he is serving as a database administrator for compass technology management in their nashville data center supporting the christian broadcasting network and northstar studios in nashville, tn.davidson has a bachelors degree from the university of tennessee at chattanooga in computer science with a minor in mathematics (though the minor in mathematics is more of an indication to how much math utc required to get the cs degree, rather than any great love or skill in the subject).the majority of his experience, with slight deviations into visual basic, has been spent with microsoft sql server from version 1.0 to the latest version that is in beta. louis primary areas of expertise are in database architecture and coding in transact-sql, and he has written numerous stored procedures and triggers throughout the years, though he seldom has the need anymore with the code generation tools he has developed to generate objects, with only a few procedures and triggers to code from the ground up.louis has published professional sql server 2000 database design, also by apress. the book follows the complete arc of developing a database through requirements, architecture, and design. he has also been an active volunteer with sql pass as a presenter and a chairperson in their special interest groups. it has been said that in his ridiculously small amount of spare time, he tends to play a lot of nintendo (got to save that darn princess, she went off and was captured by the silly dragon, again!) and watch a great deal of television. most notably, his favorites are old english programs (the avengers, the saint, monty python, and blackadder to name a few). quite often, this spare time is also spent with his notebook computer writing something pertaining to sql.robin dewson robin has come a long way since the early heady days of the sinclair zx80/81 and spectrum. he was first introduced to computers at an exhibition in glasgow where he saw a commodore pet manipulate a robot and he knew instantly that computers was the route for him. unfortunately that route initially took him into ibm mainframes.however, he feels that this is a crucial keystone in his overall approach to computing and without this he would not be where he is today. well, that, scottish college of textiles, his family and of course many, many bottles of irn bru (who needs caffeine?). robin moved to working with pcs nearly 10 years ago with foxbase and has moved from there through visual foxpro, visual basic, sybase, and of course sql server. robin can be contacted at .there are many people once again that i would like to thank for different reasons. all those at wrox throughout the years but especially cilmara, douglas, cath, helen, james, and chris for different reasons, but thanks! andy at pinball mania for keeping my pinball machines working, charlie and debbie at sea palling in norfolk for being two great friends and brilliant with my children in their arcade, phill jupitus, phil (2112 rules!) wilding, gideon coe, liz kershaw, and andrew collins at the bbcs 6music, (http:/www.bbc.co.uk/6music) for the excellent music and humor to keep me going at work, all my traders at lehmans but especially anthony jock jawad for his staunch support, and my good friend jack mason. of course special thanks though to my mum and dad for sorting out this great life for me, my sister carol and her family, eleanor, erin, and lucas out in australia but most of all my wife julie who goes through hell with each book i write. i am afraid this was not another one. but you can now go and enjoy the donny osmond concert. also my 3 long suffering kids, scott, cameron, and ellen. lets go and see a movie.up the blues. (http:/www.bedfordrugby.co.uk)chuck hawkins chuck is a senior consultant and database administrator in virginia beach, virginia. working for compass technology management, he currently manages the daily ups and downs of multiple servers for several large ministries. he has extensive experience in t-sql programming, replication, performance tuning, developer management, and routine server management.chuck has spoken at professional association for sql server conferences in chicago, london, denver, and seattle. he has contributed columns to several magazines and was a contributing author for louis davidsons professional sql server 2000 database design, now published by apress. prior to his life as a computing professional, chuck taught english in japan for five years.chuck is happily married to kathy and adopted kristina from russia five years ago on christmas day. when not working with databases, he likes to play with kristina, run long distances, and read first things journal. you can get in touch with chuck at chucksqlserver.cc.4. introductionas well as being used for fundamental database administration, stored procedures are the cornerstone of any kind of database solution. mastery of stored procedures is imperative to access and modify your data in the most effective way, as well as to keep your database secure and performing well. this book is a must on your road to that mastery.this book is dedicated to stored procedures and the surrounding aspects of sql server 2000 that affect their operation. in here you will find many useful tips, facts, and techniques that will help you to get the most from your stored procedures. whether you are accessing data from a client application, performing database administration with system stored procedures, or even using extended stored procedures to work with other parts of the operating system, you will hopefully find information that will make you review your own dealings with stored procedures, and ensure the continuous improvement of your database solutions.who is this book for?sql server 2000 administrators and designers will benefit from this book, as will experienced sql server 2000 developers who are already familiar with the basic concepts of stored procedures.4.1 book outlinebelow is a detailed outline of what this book covers, chapter by chapter.chapter 1(see 5.) stored procedure overviewwe begin by examining what constitutes a stored procedure and discuss different types of stored procedures. then we will look at the difference between system stored procedures and user stored procedures, creating and designing stored procedures, using parameters and best practices, flow control statements, recursion, and return values.chapter 2(see 6.) optimizing and debuggingin this chapter, we will discuss optimizing sql servers stored procedures. here, our focus will not be on optimizing the sql statements themselves, but instead, how sql statements can be optimized within the context of a stored procedure. we will also discuss error handling, signaling error conditions, and debugging stored procedures.chapter 3(see 7.) cursors, transactions, and concurrencyinitially, these are three topics that might seem unrelated; however, upon deeper discussion, we will discover that they are related. cursors are mechanisms to code non-set-based operations into t-sql statements. in general, their use is considered bad practice, but can be useful when building stored procedures if used properly. in this chapter we will look at accessing them from code and their typical uses. transactions are used to group together similar operations into one atomic logical operation, and we will look at look at simple, nested, and distributed transactions, and their use from stored procedures. concurrency is related to multi-tasking of operations. in databases, we are concerned with the number of queries that can be executed at the same time, especially making use of the same resources and data. we will look at locks, which are the markers used by sql server to decide whether the commands can be executed concurrently or not, and also discuss methods to maximize concurrency by using programming schemes to limit sql locking between read-only and read-write sessions.chapter 4(see 8.) typical uses of stored proceduresin this chapter, we will