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


2/17

Introduction to Stored Procedures

Defining Stored Procedures

Initial Processing of Stored Procedures

Subsequent Processing of Stored Procedures

Advantages of Stored Procedures


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


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


5/17

Advantages of Stored Procedures

Enforce Business Rules Shield Database Schema Details

Provide Security Mechanisms

Improve Performance

Reduce Network Traffic


6/17

Creating, Executing, and Modifying Stored ProceduresCreating Stored Procedures Guidelines for Creating Stored Procedures

Executing Stored Procedures

Altering and Dropping Stored Procedures


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


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


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


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


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


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


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


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


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


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


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

stored proc s

Documents