Writing Stored Procedures for Microsoft SQL Server

Writing Stored Procedures for Microsoft SQL Server

by Matthew Shepker
Released June 2000
Publisher(s): Sams
ISBN: 0672318865

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Writing Stored Procedures for Microsoft SQL Server focuses on teaching you not only how to build effective stored procedures, but also how to optimize and streamline them for long term use. Get an understanding of how SQL Server interprets stored procedures verses direct access via SQL queries. This is followed by detailed instructions on creating and maintaining a repository of stored procedures. Once you know how stored procedures are created and built, you are taught how to determine which logic within an application makes sense to be put into this scheme. Additionally, you're provided with guidance on making stored procedures that are portable across applications.

Table of contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. Introduction
  5. Introduction to Developing Stored Procedures
    1. SQL Server and Stored Procedure Background
      1. The History of Databases
      2. SQL Server's History
      3. SQL Server in Business Today
      4. Relational Database Management Systems
      5. The Features of SQL Server 2000
      6. Uses for Stored Procedures
      7. Conclusion
    2. Stored Procedure Camps
      1. What Is Business Logic?
      2. Why Differences Have Come About
      3. The All Business Logic Camp
      4. The No Business Logic Camp
      5. Finding Middle Ground
      6. Conclusion
    3. Processing Stored Procedures
      1. How SQL Server Creates Stored Procedures
      2. How SQL Server Processes Stored Procedures
      3. Execution Plans
      4. Viewing and Interpreting Execution Plans
      5. Conclusion
    4. Stored Procedures Rules
      1. Programming Stored Procedure Rules
      2. Using Naming Conventions
      3. Special Stored Procedure Naming Conventions
      4. Conclusion
  6. Simple Stored Procedures
    1. Creating and Altering Stored Procedures
      1. Creating Stored Procedures
      2. SELECT Statements
      3. Using Cursors
      4. CREATE PROCEDURE
      5. The Changing of the Code—Altering Stored Procedures
      6. Alteration Considerations
      7. Conclusion
    2. Creating More Complex Stored Procedures
      1. Complex Stored Procedure Creation
      2. Passing Parameters into Stored Procedures
      3. Returning Information to the User
      4. Conclusion
    3. Creating Stored Procedures that Insert Data
      1. Inserting Data with the INSERT Statement
      2. Inserting Data into Views
      3. Inserting Data Based on a SELECT Statement
      4. Inserting Data with an EXECUTE Statement
      5. Minimizing Blocking Conditions
      6. Automating the Creation of the Code
      7. Conclusion
    4. Creating Stored Procedures that Modify Data
      1. Verifying the Data to Be Modified
      2. Creating Procedures to Delete Data
      3. Automating Code Creation
      4. Conclusion
  7. Security and Advanced Procedure Development
    1. Providing Security for Stored Procedures
      1. Security Basics
      2. SQL Server Security
      3. Stored Procedures as a Security Mechanism
      4. Application Security
      5. Ownership Chains
      6. Conclusion
    2. Programming Structures in SQL
      1. Operators
      2. Looping Structures
      3. Control-of-Flow Statements
      4. Functions
      5. Conclusion
    3. Writing Utility Stored Procedures
      1. Defining Utility Stored Procedures
      2. Utility Stored Procedure Code
      3. Conclusion
    4. Extended Stored Procedures
      1. How Extended Stored Procedures Work
      2. Writing the Required C Code
      3. Registering an Extended Stored Procedure
      4. Useful System Extended Stored Procedures
      5. Conclusion
    5. Creating Custom Functions
      1. New Advances in SQL Server Functions
      2. User-Defined Functions
      3. Functions That Return a Table
      4. Calling User-Defined Functions
      5. Function Determinism
      6. Conclusion
  8. XML and Triggers
    1. SQL Server XML Support
      1. What Is XML?
      2. XML and HTML
      3. Microsoft and XML
      4. XML Fundamentals
      5. Using XML with SQL Server and Internet Information Server's Server Extensions
      6. SQL XML
      7. The FOR XML Clause
      8. Formatting Data with XSL
      9. XML and Style Sheets
      10. Putting It All Together
      11. Update Grams
      12. Setting Basic Security
      13. Security Configuration for URL Access
      14. Conclusion
    2. Writing Triggers
      1. How Triggers Are Defined
      2. How Triggers Are Processed
      3. Creating Triggers
      4. Nested Triggers
      5. Problems with Triggers
      6. Conclusion
    3. Considerations When Using Stored Procedures and Triggers
      1. Problems with Stored Procedures
      2. Problems with Triggers
      3. General Performance Issues
      4. Conclusion
  9. Index

Product information

  • Title: Writing Stored Procedures for Microsoft SQL Server
  • Author(s): Matthew Shepker
  • Release date: June 2000
  • Publisher(s): Sams
  • ISBN: 0672318865