Chapter 8: Implementing Stored Procedures

1
Chapter 8 Implementing Stored Procedures
2
Overview
P283

• Introduction to Stored Procedures
• Creating, Executing, Modifying, and Dropping
  Stored Procedures
• Programming Stored Procedures

3
Introduction to Stored Procedures
P284

• Purpose and Advantages of Stored Procedures
• Performance and Security
• Categories of Stored Procedures

4
Performance Security
P284-286

• No Permission validation necessary
• Code reuse
• Syntax has already been validated
• Execution plan has been created
• Compilation has already been performed
• Permissions arent necessary to underlying
  objects
• Encryption is available

5
Advantages of Stored Procedures
P284-286

• Share Application Logic
• Shield Database Schema Details
• Provide Security Mechanisms
• Improve Performance
• Reduce Network Traffic

6
Categories of Stored Procedures
P286-289

• System Stored Procedures
• Local Stored Procedures
• Temporary Stored Procedures
• Extended Stored Procedures
• Remote Stored Procedures

7
Creating, Executing, Modifying, and Dropping
Stored Procedures
P292

• How a Procedure is Stored
• Methods for Creating Stored Procedures
• Methods for Modifying Stored Procedures
• Deleting Stored Procedures

8
Initial Processing of Stored Procedures
P293
Entries into sysobjects and syscomments tables
Creation
Parsing
Execution(first timeor recompile)
Optimization
Compiled plan placed inprocedure cache
Compilation
9
Subsequent Processing of Stored Procedures
P293
10
Methods for Creating Stored Procedures
P293-299

• CREATE PROCDURE statement
• Permanent vs. Temporary
• WITH ENCRYPTION
• WITH RECOMPILE
• Enterprise Manager Wizard

11
Creating Stored Procedures
P293-299

• Create in Current Database Using the CREATE
  PROCEDURE Statement
• Can Nest to 32 Levels
• Use sp_help to Display Information

USE Northwind GO CREATE PROC dbo.OverdueOrders AS
SELECT FROM dbo.Orders WHERE
RequiredDate lt GETDATE() AND ShippedDate IS
Null GO
12
Using Input Parameters
P293-299

• Validate All Incoming Parameter Values First
• Provide Appropriate Default Values and Include
  Null Checks

CREATE PROCEDURE dbo.Year to Year Sales
_at_BeginningDate DateTime, _at_EndingDate DateTime
AS IF _at_BeginningDate IS NULL OR _at_EndingDate IS
NULL BEGIN RAISERROR('NULL values are not
allowed', 14, 1) RETURN END SELECT
O.ShippedDate, O.OrderID,
OS.Subtotal, DATENAME(yy,ShippedDate) AS
Year FROM ORDERS O INNER JOIN Order Subtotals
OS ON O.OrderID OS.OrderID WHERE
O.ShippedDate BETWEEN _at_BeginningDate AND
_at_EndingDate GO
13
Guidelines for Creating Stored Procedures

• dbo User Should Own All Stored Procedures
• One Stored Procedure for One Task
• Create, Test, and Troubleshoot
• Avoid sp_ Prefix in Stored Procedure Names
• Use Same Connection Settings for All Stored
  Procedures
• Minimize Use of Temporary Stored Procedures
• Never Delete Entries Directly From Syscomments

14
Executing Stored Procedures
P299

• Executing a Stored Procedure by Itself
• Executing a Stored Procedure Within an INSERT
  Statement

EXEC OverdueOrders
INSERT INTO Customers EXEC EmployeeCustomer
15
Executing Stored Procedures Using Input Parameters
P299

• Passing Values by Parameter Name
• Passing Values by Position

EXEC AddCustomer _at_CustomerID 'ALFKI',
_at_ContactName 'Maria Anders', _at_CompanyName
'Alfreds Futterkiste', _at_ContactTitle 'Sales
Representative', _at_Address 'Obere Str. 57',
_at_City 'Berlin', _at_PostalCode '12209',
_at_Country 'Germany', _at_Phone
'030-0074321'
EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste',
'Maria Anders', 'Sales Representative', 'Obere
Str. 57', 'Berlin', NULL, '12209', 'Germany',
'030-0074321'
16
Returning Values Using Output Parameters
P299
CREATE PROCEDURE dbo.MathTutor _at_m1 smallint,
_at_m2 smallint, _at_result smallint OUTPUT AS
SET _at_result _at_m1 _at_m2 GO DECLARE _at_answer
smallint EXECUTE MathTutor 5,6, _at_answer
OUTPUT SELECT 'The result is ', _at_answer The
result is 30
Creating Stored Procedure
Executing Stored Procedure
Results of Stored Procedure
17
Explicitly Recompiling Stored Procedures
P299

• Recompile When
• Stored procedure returns widely varying result
  sets
• A new index is added to an underlying table
• The parameter value is atypical
• Recompile by Using
• CREATE PROCEDURE WITH RECOMPILE
• EXECUTE WITH RECOMPILE
• sp_recompile

18
Executing Extended Stored Procedures
P299

• Are Programmed Using Open Data Services API
• Can Include C and C Features
• Can Contain Multiple Functions
• Can Be Called from a Client or SQL Server
• Can Be Added to the master Database Only

EXEC master..xp_cmdshell 'dir c\'
19
Altering and Dropping Stored Procedures
P302-304

• Altering Stored Procedures
• Include any options in ALTER PROCEDURE
• Does not affect nested stored procedures
• Dropping stored procedures
• Execute the sp_depends stored procedure to
  determine whether objects depend on the stored
  procedure

USE Northwind GO ALTER PROC dbo.OverdueOrders AS S
ELECT CONVERT(char(8), RequiredDate, 1)
RequiredDate, CONVERT(char(8), OrderDate, 1)
OrderDate, OrderID, CustomerID, EmployeeID
FROM Orders WHERE RequiredDate lt GETDATE() AND
ShippedDate IS Null ORDER BY RequiredDate GO
20
Handling Error Messages
P312

• RETURN Statement Exits Query or Procedure
  Unconditionally
• sp_addmessage Creates Custom Error Messages
• _at__at_error Contains Error Number for Last Executed
  Statement
• RAISERROR Statement
• Returns user-defined or system error message
• Sets system flag to record error

21
Performance Considerations

• Windows 2000 System Monitor
• Object SQL Server Cache Manager
• Object SQL Statistics
• SQL Profiler
• Can monitor events
• Can test each statement in a stored procedure

22
Recommended Practices
23
Review

• Introduction to Stored Procedures
• Creating, Executing, Modifying, and Dropping
  Stored Procedures
• Programming Stored Procedures

24
Lab Implementing Stored Procedures
25
Lab Implementing Stored Procedures

• Exercise 1
• Pages 289-291 Exploring Stored Procedures
• Exercise 2
• Pages 304-308 Working with Stored Procedures