Module 9: Implementing Stored Procedures
Overview
Introduction to Stored Procedures
Creating, Executing, Modifying, and Dropping Stored Procedures
Using Parameters in Stored Procedures
Executing Extended Stored Procedures
Handling Error Messages
Introduction to Stored Procedures
Defining Stored Procedures
Initial Processing of Stored Procedures
Subsequent Processing of Stored Procedures
Advantages of Stored Procedures
Defining Stored Procedures
Named Collections of Transact-SQL Statements
Encapsulate Repetitive Tasks
Five Types (System, Local, Temporary, Remote, and Extended)
Accept Input Parameters and Return Values
Return Status Value to Indicate Success or Failure
Initial Processing of Stored Procedures Creation Creation
Parsing Parsing
Execution Execution
Optimization Optimization
(first (firsttime time or orrecompile) recompile)
Compilation Compilation
Entries Entries into into sysobjects sysobjects and and syscomments syscomments tables tables
Compiled Compiled plan plan placed placed in in procedure procedure cache cache
Subsequent Processing of Stored Procedures Execution Plan Retrieved Execution Plan
Execution Context Connection 1
SELECT * FROM dbo.member WHERE member_no = ?
8082 Connection 2
24 Connection 3
1003
Unused plan is aged out
Advantages of Stored Procedures
Share Application Logic
Shield Database Schema Details
Provide Security Mechanisms
Improve Performance
Reduce Network Traffic
Creating, Executing, Modifying, and Dropping
Stored Procedures
Creating Stored Procedures
Guidelines for Creating Stored Procedures
Executing Stored Procedures
Altering and Dropping Stored Procedures
Creating Stored Procedures
Create in Current Database Using the CREATE PROCEDURE Statement
USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null GO
Can Nest to 32 Levels
Use sp_help to Display Information
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
Executing Stored Procedures
Executing a Stored Procedure by Itself EXEC OverdueOrders
Executing a Stored Procedure Within an INSERT Statement INSERT INTO Customers EXEC EmployeeCustomer
Altering and Dropping Stored Procedures
Altering Stored Procedures
Include any options in ALTER PROCEDURE
Does not affect nested stored procedures
USE USE Northwind Northwind GO GO ALTER ALTER PROC PROC dbo.OverdueOrders dbo.OverdueOrders AS AS SELECT SELECT CONVERT(char(8), CONVERT(char(8), RequiredDate, RequiredDate, 1) 1) RequiredDate, RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, OrderID, CustomerID, CustomerID, EmployeeID EmployeeID FROM Orders FROM Orders WHERE WHERE RequiredDate RequiredDate << GETDATE() GETDATE() AND AND ShippedDate ShippedDate IS IS Null Null ORDER BY RequiredDate ORDER BY RequiredDate GO GO
Dropping stored procedures
Execute the sp_depends stored procedure to determine whether objects depend on the stored procedure
Lab A: Creating Stored Procedures
Using Parameters in Stored Procedures
Using Input Parameters
Executing Stored Procedures Using Input Parameters
Returning Values Using Output Parameters
Explicitly Recompiling Stored Procedures
Using Input Parameters
Validate All Incoming Parameter Values First
Provide Appropriate Default Values and Include Null Checks
CREATE CREATE PROCEDURE PROCEDURE dbo.[Year dbo.[Year to to Year Year Sales] Sales] @BeginningDate DateTime, @EndingDate @BeginningDate DateTime, @EndingDate DateTime DateTime AS AS IF IF @BeginningDate @BeginningDate IS IS NULL NULL OR OR @EndingDate @EndingDate IS IS NULL NULL BEGIN BEGIN RAISERROR('NULL RAISERROR('NULL values values are are not not allowed', allowed', 14, 14, 1) 1) RETURN RETURN END END SELECT SELECT O.ShippedDate, O.ShippedDate, O.OrderID, O.OrderID, OS.Subtotal, OS.Subtotal, DATENAME(yy,ShippedDate) DATENAME(yy,ShippedDate) AS AS Year Year FROM ORDERS O INNER JOIN [Order Subtotals] FROM ORDERS O INNER JOIN [Order Subtotals] OS OS ON O.OrderID = OS.OrderID ON O.OrderID = OS.OrderID WHERE WHERE O.ShippedDate O.ShippedDate BETWEEN BETWEEN @BeginningDate @BeginningDate AND AND @EndingDate @EndingDate GO GO
Executing Stored Procedures Using Input Parameters ď Ž
Passing Values by Parameter Name EXEC EXEC AddCustomer AddCustomer @CustomerID @CustomerID == 'ALFKI', 'ALFKI', @ContactName @ContactName == 'Maria 'Maria Anders', Anders', @CompanyName @CompanyName == 'Alfreds 'Alfreds Futterkiste', Futterkiste', @ContactTitle @ContactTitle == 'Sales 'Sales Representative', Representative', @Address = 'Obere Str. 57', @Address = 'Obere Str. 57', @City @City == 'Berlin', 'Berlin', @PostalCode @PostalCode == '12209', '12209', @Country @Country == 'Germany', 'Germany', @Phone @Phone == '030-0074321' '030-0074321'
ď Ž
Passing Values by Position EXEC EXEC AddCustomer AddCustomer 'ALFKI2', 'ALFKI2', 'Alfreds 'Alfreds Futterkiste', Futterkiste', 'Maria 'Maria Anders', Anders', 'Sales 'Sales Representative', Representative', 'Obere 'Obere Str. Str. 57', 57', 'Berlin', 'Berlin', NULL, NULL, '12209', '12209', 'Germany', 'Germany', '030-0074321' '030-0074321'
Returning Values Using Output Parameters Creating CreatingStored Stored Procedure Procedure
Executing ExecutingStored Stored Procedure Procedure
Results ResultsofofStored Stored Procedure Procedure
CREATE CREATE PROCEDURE PROCEDURE dbo.MathTutor dbo.MathTutor @m1 @m1 smallint, smallint, @m2 @m2 smallint, smallint, @result @result smallint smallint OUTPUT OUTPUT AS AS SET SET @result @result == @m1* @m1* @m2 @m2 GO GO DECLARE DECLARE @answer @answer smallint smallint EXECUTE EXECUTE MathTutor MathTutor 5,6, 5,6, @answer @answer OUTPUT OUTPUT SELECT SELECT 'The 'The result result is: is: ', ', @answer @answer The The result result is: is: 30 30
Explicitly Recompiling Stored Procedures
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
Executing Extended Stored Procedures
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:\'
Handling Error Messages
RETURN Statement Exits Query or Procedure Unconditionally
sp_addmessage Creates Custom Error Messages
@@error Contains Error Number for Last Executed Statement
RAISERROR Statement
Returns user-defined or system error message
Sets system flag to record error
Demonstration: Handling Error Messages
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
Recommended Practices Verify Verify Input Input Parameters Parameters Design Design Each Each Stored Stored Procedure Procedure to to Accomplish Accomplish aa Single Single Task Task Validate Validate Data Data Before Before You You Begin Begin Transactions Transactions Use Use the the Same Same Connection Connection Settings Settings for for All All Stored Stored Procedures Procedures Use Use WITH WITH ENCRYPTION ENCRYPTION to to Hide Hide Text Text of of Stored Stored Procedures Procedures
Lab B: Creating Stored Procedures Using Parameters
Review
Introduction to Stored Procedures
Creating, Executing, Modifying, and Dropping Stored Procedures
Using Parameters in Stored Procedures
Executing Extended Stored Procedures
Handling Error Messages