Implementing Stored
Procedures
Overview
Introducing Stored Procedures
Creating, Modifying, Dropping, and Executing
Stored Procedures
Using Parameters in Stored Procedures
Handling Error Messages
Working with Stored Procedures
Lesson: Introducing Stored Procedures
What Are Stored Procedures?
Advantages of Stored Procedures
Initial Processing of Stored Procedures
Subsequent Processing of Stored Procedures
What Are Stored Procedures?
procedures is a set of TSql statements which is
executed as single unit
Accept Input Parameters and Return Output
Parameter Values
Advantages of Stored Procedures
Makes application Loosely coupled
Provide Security Mechanisms
Improve Performance
Reduce Network Traffic
Initial Processing of Stored Procedures
Creation
Entries into sysobjects
Parsing
and syscomments tables
Execution
(first time or Optimization
recompile)
Compiled plan placed in
Compilation
procedure cache
Subsequent Processing of Stored Procedures
Execution Plan Retrieved
Execution Plan Execution Context
8082
Connection 1
SELECT *
FROM
[Link] 24
WHERE Connection 2
member_no = ?
1003
Connection 3
Unused plan is aged out
Lesson: Creating, Modifying, Dropping, and
Executing Stored Procedures
The CREATE PROCEDURE Statement
Guidelines for Creating Stored Procedures
The ALTER PROCEDURE Statement
The DROP PROCEDURE Statement
Stored Procedure Execution
The CREATE PROCEDURE Statement
Create in Current Database Using the CREATE
PROCEDURE Statement
USE Northwind
GO
CREATE PROC [Link]
AS
SELECT *
FROM [Link]
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 Objects
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
The ALTER PROCEDURE Statement
Altering Stored Procedures
Include any options in ALTER PROCEDURE
Does not affect nested stored procedures
USE Northwind
GO
ALTER PROC [Link]
AS
SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate,
CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID
FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
ORDER BY RequiredDate
GO
The DROP PROCEDURE Statement
Dropping Stored Procedures
Execute the sp_depends stored procedure to determine
whether objects depend on the stored procedure
Procedure information is removed from the sysobjects and
syscomments system tables
USE Northwind
GO
DROP PROC [Link]
GO
Stored Procedure Execution
Executing a Stored Procedure by Itself
EXEC OverdueOrders
Executing a Stored Procedure Within an
INSERT Statement
INSERT INTO Customers
EXEC EmployeeCustomer
Lab A: Creating Stored Procedures
Exercise 1: Writing and Executing a
Stored Procedure
Exercise 2: Locating Stored
Procedure Information
Lesson: Using Parameters in Stored Procedures
Input Parameters
Methods of Setting Parameter Values
Return Values Using OUTPUT Parameters
Return Values Using the RETURN Statement
Stored Procedure Recompile
Input Parameters
Validate All CREATE PROCEDURE dbo.[Year to Year Sales]
@BeginDate DateTime = Null,
Incoming @EndDate DateTime = Null
AS
Parameter IF @BeginDate IS Null
Values First SET @BeginDate = dateadd(yy,-1,GetDate())
IF @EndDate IS Null
SET @EndDate = GetDate()
Provide
Appropriate IF Datediff(dd,@BeginDate,@EndDate) > 365
BEGIN
Default Values print('The maximum timespan allowed for
this report is one year.')
and Include RETURN
Null Checks END
SELECT [Link],[Link],[Link],
DATENAME(yy,ShippedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON [Link] = [Link]
WHERE [Link]
BETWEEN @BeginDate AND @EndDate
GO
Methods of Setting Parameter Values
Passing Values by Parameter Name
EXEC AddCustomer
@CustomerID = 'ALFKI',
@ContactName = 'Maria Anders',
@CompanyName = 'Alfreds Futterkiste',
@ContactTitle = 'Sales Representative',
@Address = 'Obere Str. 57',
@City = 'Berlin',
@PostalCode = '12209',
@Country = 'Germany',
@Phone = '030-0074321'
Passing Values by Position
EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste',
'Maria Anders', 'Sales Representative', 'Obere Str.
57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'
Return Values Using OUTPUT Parameters
CREATE PROCEDURE [Link]
@m1 smallint,
Creating Stored @m2 smallint,
Procedure @result int OUTPUT
AS
SET @result = @m1 * @m2
GO
DECLARE @answer smallint
Executing Stored EXECUTE MathTutor 5,6, @answer
Procedure OUTPUT
SELECT 'The result is: ', @answer
Results of Stored
Procedure The result is: 30
Return Values Using the RETURN Statement
CREATE PROC [Link](
@LastName nvarchar(20),
@FirstName nvarchar(10) ) AS
INSERT Employees(LastName,FirstName)
Creating Stored VALUES (@LastName, @FirstName)
Procedure
RETURN SCOPE_IDENTITY()
Go
DECLARE @NewEmployeeId int
EXEC @NewEmployeeId = [Link]
@LastName='Hankin', @FirsName='Alex'
Executing Stored
SELECT EmployeeID, LastName, FirstName
Procedure FROM Employees
WHERE EmployeeId = @NewEmployeeId
Result EmployeeID LastName FirstName
----------- -------------------- ----------
10 Hankin Alex
Stored Procedure Recompile
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
Lesson: Handling Error Messages
Error Messages
Demonstration: Handling Error Messages
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
Handling error messages
Dynamic SQL in Stored Procedures
Dynamic Search Conditions
declare @str nvarchar(60),@WhereCondition nvarchar(60)
SELECT @str = 'SELECT * FROM CUSTOMER WHERE 1=1'
IF LEN(@WhereCondition) > 0
SELECT @str = @str + @WhereCondition
EXEC sp_executesql @str
The IN Clause
SELECT @SQL =
'SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE ProductID IN (' + (@ProductIDs) + ')'
Administrative Functions
Lab B: Creating Stored Procedures Using Parameters
Exercise 1: Using the Create Stored
Procedure Wizard
Exercise 2: Using Error Handling in
Stored Procedures
Exercise 3: Customizing Error Messages
Exercise 4: Using Return Codes
If Time Permits
Executing Extended Stored Procedures
Tracing Stored Procedures Using
SQL Profiler