Writing Stored Procedures for Microsoft SQL Server

Matthew Shepker is an MCSE as well as a Microsoft Certified Trainer (MCT). He is a Microsoft SQL Server consultant and network integrator in Overland Park, Kansas. Matthew has been working with SQL Server for over 4 years in a variety of business applications including online.

Introduction.
I. INTRODUCTION TO DEVELOPING STORED PROCEDURES.

1. SQL Server and Stored Procedure Background.


The Old Staple Functionality. New Functionality.



Uses for Stored Procedures. Conclusion.

2. Stored Procedure Camps.


What Is Business Logic? 9. Why Differences Have Come About.



The All Business Logic Camp. Advantages of Business Logic in the Database. Disadvantages of Business Logic in the Database.



The No Business Logic Camp. Advantages of the No Business Logic Camp. Disadvantages of the No Business Logic Camp.



Finding Middle Ground. Conclusion.

3. Processing Stored Procedures.


How SQL Server Creates Stored Procedures. How SQL Server Processes Stored Procedures.



Deferred Name Resolution. Creation of an Execution Plan.



Execution Plans.



Execution Plan Makeup. How SQL Server Chooses Query Plans. Aging and Deallocating Execution Plans. Execution Plan Recompilation.



Viewing and Interpreting Execution Plans. Conclusion.

4. Stored Procedures Rules.


Programming Stored Procedure Rules.



Basic Rules. Referencing Objects in Stored Procedures. Source Code Protection. ODBC Options. Deferred Name Resolution and Query Plan Creation.



Using Naming Conventions.



Microsoft Discrepancies in Naming Convention. Microsoft Identifier Rules. Suggested Naming Conventions.



Special Stored Procedure Naming Conventions. Conclusion.

II. SIMPLE STORED PROCEDURES.

5. Creating and Altering Stored Procedures.


Creating Stored Procedures. SELECT Statements.



Limiting the Number of Columns. Limiting the Number of Rows.



Using Cursors.



Creating Cursors in SQL Server. Using the DECLARE Statement.



CREATE PROCEDURE. The Changing of the Code—Altering Stored Procedures.



The Old Way. The New Way—ALTER PROCEDURE.



Alteration Considerations. Conclusion.

6. Creating More Complex Stored Procedures.


Complex Stored Procedure Creation. Passing Parameters into Stored Procedures. Returning Information to the User.



The RETURN Statement. The PRINT Command. The RAISERROR Function. OUTPUT Parameters.



Conclusion.

7. Creating Stored Procedures that Insert Data.


Inserting Data with the INSERT Statement. Inserting Data into Views. Inserting Data Based on a SELECT Statement. Inserting Data with an EXECUTE Statement. Minimizing Blocking Conditions. Automating the Creation of the Code. Conclusion.

8. Creating Stored Procedures that Modify Data.


Verifying the Data to Be Modified. Creating Procedures to Delete Data. Automating Code Creation. Conclusion.

III. SECURITY AND ADVANCED PROCEDURE DEVELOPMENT.

9. Providing Security for Stored Procedures.


Security Basics. SQL Server Security.



Logins. Users. Roles. Permissions. Permission States. Assigning Permissions. Adding Logins and Users.



Stored Procedures as a Security Mechanism. Application Security. Ownership Chains. Conclusion.

10. Programming Structures in SQL.


Operators.



Assignment Operator. Arithmetic Operators. Comparison Operators. Logical Operators. String Concatenation Operator. Bitwise Operators. Unary Operators.



Looping Structures.



WHILE Loop. GOTO Loop.



Control-of-Flow Statements.



IF ELSE. CASE.



Functions.



String Functions. Date Functions. Mathematical Functions. System Functions.



Conclusion.

11. Writing Utility Stored Procedures.


Defining Utility Stored Procedures. Utility Stored Procedure Code.



Updating Index Statistics. Reindexing a Database. Finding Object Permissions. Finding Database-Level Permissions. The New sp_who. Checking for Blocks. Checking Table Sizes. Numbering Source Code Lines. Retrieving Index Members.



Conclusion.

12. Extended Stored Procedures.


How Extended Stored Procedures Work. Writing the Required C Code. Registering an Extended Stored Procedure. Useful System Extended Stored Procedures. Conclusion.

13. Creating Custom Functions.


New Advances in SQL Server Functions. User-Defined Functions. Functions That Return a Table.



Multi-Statement Tabular Functions. In-Line Functions.



Calling User-Defined Functions. Calling Scalar Functions. Calling Tabular Functions. Calling Built-in Functions.



Function Determinism. User-Defined Function Determinism. Determinism of System Functions.



Conclusion.

IV. XML AND TRIGGERS.

14. SQL Server XML Support.


What Is XML? 9. XML and HTML. Microsoft and XML. XML Fundamentals.



A Basic XML Demonstration.



Using XML with SQL Server and Internet Information Server's Server. Extensions.



Creating a Test Database. Installing the Server Extensions. Using the Registration Tool Snap-in (Regxmlss Utility).



SQL XML.



Creating a URL Query. Canning a Query. Passing Parameters.



The FOR XML Clause.



FOR XML RAW. FOR XML AUTO. FOR XML EXPLICIT.



Formatting Data with XSL.



XSL Versus CSS.



XML and Style Sheets.



Enabling an XML Document for Browsing with Style Sheets.



Putting it All Together.



Creating the XML Template File. Creating the Style Sheet. Creating the HTML Document.



Update Grams.



The Update Gram Standard Format.



Setting Basic Security. Security Configuration for URL Access.



Using IIS Directory Security Settings.



Conclusion.

15. Writing Triggers 297.


How Triggers Are Defined. How Triggers Are Processed.



AFTER Triggers. INSTEAD OF Triggers.



Creating Triggers.



Building an AFTER Trigger. Building INSTEAD OF Triggers. Trigger Restrictions.



Nested Triggers. Problems with Triggers. Conclusion.

16. Considerations When Using Stored Procedures and Triggers.


Problems with Stored Procedures.



Blocking. Sequential Numbering.



Problems with Triggers. General Performance Issues. Conclusion.

Index.