Download - Stored Proc s
-
7/28/2019 Stored Proc s
1/17
Overview
Introduction to Stored Procedures Creating, Executing, and Modifying Stored Procedures
Using Parameters in Stored Procedures
Executing Extended Stored Procedures
Handling Error Messages
-
7/28/2019 Stored Proc s
2/17
Introduction to Stored Procedures
Defining Stored Procedures
Initial Processing of Stored Procedures
Subsequent Processing of Stored Procedures
Advantages of Stored Procedures
-
7/28/2019 Stored Proc s
3/17
Defining Stored Procedures
Named Collections of T-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
-
7/28/2019 Stored Proc s
4/17
Initial Processing of Stored Procedures
ParsingCreation Entries into sysobjectsand syscomments tables
Optimization
Compilation
Execution(first time
or recompile)
Compiled plan placed in
procedure cache
Subsequent Processing of Stored Procedures
-
7/28/2019 Stored Proc s
5/17
Advantages of Stored Procedures
Enforce Business Rules Shield Database Schema Details
Provide Security Mechanisms
Improve Performance
Reduce Network Traffic
-
7/28/2019 Stored Proc s
6/17
Creating, Executing, and Modifying Stored ProceduresCreating Stored Procedures Guidelines for Creating Stored Procedures
Executing Stored Procedures
Altering and Dropping Stored Procedures
-
7/28/2019 Stored Proc s
7/17
Creating Stored ProceduresCreate in Current Database with the CREATE
PROCEDURE Statement
Can Nest to 32 Levels
Use sp_help to Display Information
Use library
GO
CREATE PROC dbo.overdue_booksAS
SELECT * FROM dbo.loan
WHERE due_date < GETDATE()
GO
-
7/28/2019 Stored Proc s
8/17
Guidelines for Creating Stored Procedures
dbo User Should Own All Stored Procedures One Stored Procedure for One Task
Create, Test, and Debug on Server
Avoid sp_Prefix in Stored Procedure Names
Use Same Connection Settings for All StoredProcedures
Minimize Use of Temporary Stored Procedures
-
7/28/2019 Stored Proc s
9/17
Executing Stored ProceduresExecuting a Stored Procedure by Itself
Executing a Stored Procedure Within an INSERTStatement
EXEC overdue_books
INSERT INTO customersEXEC employee_customer
-
7/28/2019 Stored Proc s
10/17
Altering and Dropping Stored Procedures
Altering Stored Procedures Include any options in ALTER PROCEDURE
Does not affect nested stored procedures
Dropping Stored Procedures
ALTER PROC overdue_books
AS
SELECT CONVERT(char(8), due_date, 1) date_due, isbn, copy_no,
SUBSTRING(title,1,30) title, member_no, lastname
FROM OverdueViewORDER BY due_date
-
7/28/2019 Stored Proc s
11/17
Using Parameters in Stored Procedures
Using Input Parameters Executing Stored Procedures with Input Parameters
Returning Values with Output Parameters
Explicitly Recompiling Stored Procedures
-
7/28/2019 Stored Proc s
12/17
Using Input Parameters
Trap Missing or Invalid Parameter Values First Provide Appropriate Default Values
CREATE PROC dbo.find_isbn@title longstring = null,
@translation char(8) = English
AS
IF @title is null
BEGIN
PRINT Please provide a title (or partial title)
and the translation
PRINT find_isbn Oliver%, Japanese
.
.
END
-
7/28/2019 Stored Proc s
13/17
Executing Stored Procedures with Input Parameters
Passing Values by Reference
Passing Values by Position
EXEC addadult
@firstname = Linda,
@lastname = LaBrie,
@Street = Dogwood Drive,@city = Sacramento,
@state = CA,
@zip = 94203
-
7/28/2019 Stored Proc s
14/17
Returning Values with Output ParametersCREATE PROC dbo.mathtutor
@m1 smallint,
@m2 smallint,
@result smallint OUTPUT
AS
SET @result = @m1 * @m2
DECLARE @answer smallint
EXEC mathtutor 5,6,@answer OUTPUT
SELECT The result is : , @answer
The result is : 30
-
7/28/2019 Stored Proc s
15/17
Explicitly Recompiling Stored ProceduresRecompile When
Stored procedure returns widely varying result sets
A new index is added to an underlying table
The parameter value is a typical
Recompile by Using
CREATE PROCEDURE [WITH RECOMPILE]
EXECUTE [WITH RECOMPILE]
sp_recompile
-
7/28/2019 Stored Proc s
16/17
Executing Extended Stored Procedures
Increase SQL Server FunctionalityAre 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:\mssql7
-
7/28/2019 Stored Proc s
17/17
Handling Error MessagesRETURN Statement Exists Query or Procedures
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