sql xp 09

©NIIT SQL/Lesson 9/Slide 1 of 61

Implementing Stored Procedures

Objectives

In this lesson, you will learn to:

Create a stored procedure

Execute a stored procedure

Pass parameters to a stored procedure

Modify a stored procedure

Return values from a stored procedure

Return multiple values from a stored procedure

Call a procedure from another stored procedure

Recompile a stored procedure

Drop a stored procedure



Getting Started

A stored procedure is a collection or batch of T-SQL statements and control-of-flow language that is stored under one name, and executed as a single unit

Benefits of Stored Procedures Improved performance Reduction in network congestion Better consistency Better security mechanism



Getting Started (Contd.)

Types of Procedures

User-defined

System

Temporary

Remote

Extended



Just a Minute…

What are the benefits of using stored procedures?



9.D.1 Speeding up the Execution of Batch Statements

A list containing the name, address, city, zip code, telephone number, and the fax number of recruitment agencies is required frequently. Recently, it has been noticed that there is a delay in generating this report due to the network congestion. Besides, the Human Resources department personnel make mistakes while querying for this information. Suggest and implement a solution.



Task List

Identify the object that can be used to solve the above problem

Draft the stored procedure on paper

Create the stored procedure in the database

Check the existence of the procedure in the database

Execute the procedure



Identify the object that can be used to solve the above Problem

Result:

Use a stored procedure to reduce network traffic and reduce the errors committed by the HR personnel



Draft the stored procedure on paper

The CREATE PROCEDURE Statement

SyntaxCREATE PROCEDURE proc_nameASBEGIN

sql_statement1sql_statement2

END



Draft the stored procedure on paper (Contd.)

Result:

The database in which the stored procedure has to be created is Recruitment

The type of procedure is user-defined

The name for the stored procedure is prcPrintRecruitmentAgencyList




Action:

In the Query Analyzer window, type:CREATE PROCEDURE

prcPrintRecruitmentAgencyListASBEGINPRINT 'List of Recruitment Agencies'SELECT cName, vAddress, cCity, cZip,

cPhone, cFax FROM RecruitmentAgenciesEND

Press F5 to execute the statement




The sp_helptext Command is used to check the existence of a procedure in the database

Syntax

sp_helptext proc_name

Action

In the Query Analyzer window, type:

sp_helptext prcPrintRecruitmentAgencyList

Check the result. The output must be the code that you wrote to create the procedure




The EXECUTE PROCEDURE statement is used to execute the stored procedure

Syntax

EXECUTE proc_name

or

EXEC proc_name

or

proc_name



Execute the procedure (Contd.)

Action:


EXECUTE prcPrintRecruitmentAgencyList

or

EXEC prcPrintRecruitmentAgencyList

or

prcPrintRecruitmentAgencyList

Press F5 to execute the command



Just a Minute…

The query to obtain the list of candidates and their recruitment agencies is:

SELECT 'Candidate Name' = vFirstName , 'Recruitment Agency' = cName

FROM ExternalCandidate

JOIN RecruitmentAgencies

ON ExternalCAndidate.cAgencyCode = RecruitmentAgencies.cAgencyCode

Create a stored procedure for the same.



9.D.2 Creating a Generic Stored Procedure

Information on contract recruiters in a particular city is required frequently.The city for which details are required

changes from time to time. Create a stored procedure that will generate information for a specified city



Task List

Identify a method to write a generic procedure that will generate results for variable inputs

Draft statements to create a procedure



Prepare test cases with existing and non-existing values

Execute the procedure with the existing value

Execute the procedure with the non-existing value



Identify a method to write a generic procedure that will generate results for variable inputs Parameter

A parameter is a placeholder in a query or a stored procedure that accepts a user-defined value whenever the query or stored procedure is executed

Types of Parameters

Input parameters

Output parameters

Result:

As the city name will be supplied by the user, use input parameters



Draft statements to create a procedure

Result:

The variable name is @cCity

The datatype of the variable is char of size 15. So, the variable needs to be declared, as shown below:

@cCity char(15)




Action:

In the Query Analyzer window, type:CREATE PROC prcListContractRecruiter @cCity char(15)ASBEGIN PRINT 'List of Contract Recruiters' SELECT cName,cCity,cZip,cPhone FROM ContractRecruiter WHERE cCity = @cCityEND

Press F5 to execute the query




Action:


sp_helptext prcListContractRecruiter

Check the result

Prepare test cases with the existing and non-existing values

Action:

Test for a city, which exists in the ContractRecruiter table: Alexandria

Test for a city, which does not exist in the ContractRecruiter table: Boston



Execute the procedure with the existing value

Action:


prcListContractRecruiter Alexandria

Press F5 to execute the procedure

Execute the procedure with the non-existing value

Action:


prcListContractRecruiter Boston

Press F5 to execute the procedure



9.D.3 Modifying a Stored Procedure Details of the recruitment agencies for a particular city are

required. The city for which the data is required varies from time to time. Instead of creating a new procedure, modify the existing prcPrintRecruitmentAgencyList procedure to meet this requirement. In the new procedure, if no value is passed to the procedure, it should display 'Usage: prcPrintRecruitmentAgencyList <city>' and stop execution.



Task List

Identify a method to modify the existing procedure

Draft statements to modify a procedure

Verify that the procedure has been modified





The ALTER PROCEDURE Statement is used to modify an existing procedure.

The Default Parameter

You can use default parameters to pass value to the stored procedure in case no value is passed to it.

The value passed as default value must be a constant value or NULL.

Result:

Use the ALTER PROCEDURE statement to modify the procedure.



Identify a method to modify the existing procedure (Contd.)

Use NULL as the default parameter to the procedure. If no value is passed to the procedure parameter, the parameter would contain a NULL value. If the value of the parameter is NULL, the procedure should display a message explaining the usage of the procedure.

Draft statements to modify a procedure

Action:


ALTER PROCEDURE prcPrintRecruitmentAgencyList @city char(15)=NULL



Draft statements to modify a procedure (Contd.)AS BEGINIF @city is nullBEGIN

PRINT 'Usage: prcPrintRecruitmentAgencyList <city>'RETURN

ENDPRINT 'List of Recruitment Agencies'SELECT cName,vAddress,cZip,cPhone FROM RecruitmentAgencies WHERE cCity = @cityEND



Draft statements to modify a procedure (Contd.)


In the above code, the RETURN statement passes the control back to the position from where it was called



Verify that the procedure has been modified

Action:


sp_helptext prcPrintrecruitmentAgencyList



Action:


EXEC prcPrintRecruitmentAgencyList Alexandria




Execute the procedure (Contd.)

In case you do not pass any parameter to the procedure by typing:

prcPrintRecruitmentAgencyList

you will get the following output:

Usage: prcPrintRecruitmentAgencyList <city>



9.D.4 Notifying Successful Execution

Modify the prcPrintRecruitmentAgencyList procedure to notify users of its successful execution



Task List

Identify a method to write a procedure that will return a value

Draft the procedure

Modify the procedure in the database

Check that the procedure has been modified in the database




Identify a method to write a procedure that will return a value

The RETURN Keyword

You use the RETURN statement to return values from a stored procedure

Syntax

RETURN value

Result:

The RETURN statement can be used to return values from a stored procedure



Draft the procedure

Result:

The batch statements for creating the stored procedure are as follows:

ALTER PROCEDURE prcPrintRecruitmentAgencyList @city char(15)

AS

BEGIN

IF EXISTS(SELECT * FROM RecruitmentAgencies

WHERE cCity=@city)



Draft the procedure (Contd.)

BEGIN

PRINT 'List of Recruitment Agencies'

SELECT cName,vAddress,cZip,cPhone

FROM RecruitmentAgencies

WHERE cCity = @city

Return 0

END

ELSE BEGIN

PRINT 'No Records Found for given city' RETURN 1

END

END



Modify the procedure in the database

Action:

In the Query Analyzer window, type prcPrintRecruitmentAgencyList




Check that the procedure has been modified in the database

Action:


sp_helptext prcPrintRecruitmentAgencyList





Action:


DECLARE @ReturnValue int

EXEC @ReturnValue = prcPrintRecruitmentAgencyList 'Alexandria'

SELECT @ReturnValue

Press F5 to execute the statement. When the above statements are executed, the value returned by the procedure prcPrintRecruitmentAgencyList would be stored in the variable @ReturnValue. This value would be displayed by the SELECT statement



9.D.5 Calling One Procedure From Another

Details about the recruitment agencies and the contract recruiters in a city are required in a single report. Create a single procedure that uses the existing procedures for the report



Task List


Create the procedure in the database


Execute the procedure with parameters



Identify a method to write a procedure that will use an existing procedure

Nested Procedures

You can execute or call one procedure from within another procedure

Result:

Create a new stored procedure that will call the existing procedures




Action:


CREATE PROCEDURE prcDisplayBoth

@city char(15)

AS

BEGIN

EXEC prcListContractRecruiter @city

EXEC prcPrintRecruitmentAgencyList @city

END




Check the existence of the procedure in the database Action:


sp_helptext prcDisplayBoth Press F5 to execute the statement


Action:


prcDisplayBoth 'Alexandria'




Just a Minute…

You need to modify a stored procedure. Which command will you use to modify the procedure?



9.D.6 Returning Multiple Output Values

A formatted report of the status of positions available in the company is to be displayed. Create a procedure, prcGetPositionDetail which will return the position description, the budgeted manpower and the current strength of employees for a given position. Create another procedure called prcDisplayPositionStatus that will display cRequisitionCode, vRegion, and siNoOfVacancy from the Requisition table. The procedure prcDisplayPositionStatus should also call the prcGetPositionDetail procedure and display the values returned by it for position '0002' in the following format:



9.D.6 Returning Multiple Output Values (Contd.)

The Status for the Position: Maketing Manager

Budgeted Strength : 100

Current Strength : 83

cRequisitionCode vRegion siNoOfVacancy

---------------- -------------------- ----------

000002 Texas 11



Task List

Identify a method to write a generic procedure that will return more than one value


Check whether the procedure exists in the database




Identify a method to write a generic procedure that will return more than one value

The OUTPUT Parameter - is used to return more than one value from the procedure

Syntax

CREATE PROC[EDURE] procedure_name [ {@parameter data_type} [OUTPUT]

]ASsql_statement [...n]

Result:

Use CREATE PROCEDURE with the OUTPUT option




Action:

In the Query Analyzer Window, type the statements given below.

CREATE PROCEDURE prcGetPositionDetail

@Pcode char(4), @Description char(30) OUTPUT,

@budget int OUTPUT, @CurrentStrength int OUTPUT

AS

BEGIN

IF EXISTS(SELECT * FROM Position WHERE cPositionCode = @PCode)



Create the procedure in the database (Contd.)BEGIN

SELECT @Description = vDescription,@budget = iBudgetedStrength, @CurrentStrength = iCurrentStrength

FROM Position WHERE cPositionCode=@Pcode RETURN 0ENDELSE

RETURN 1END

Press F5 to execute



Create the procedure in the database (Contd.)

In the Query Analyzer Window, type:

CREATE PROCEDURE prcDisplayPositionStatus @PCode char(4)ASBEGINDECLARE @Description char(30)DECLARE @budget intDECLARE @CurrentStrength intDECLARE @ReturnValue intEXEC @ReturnValue = prcGetPositionDetail @Pcode,@Description output,@Budget output,@CurrentStrength output



Create the procedure in the database (Contd.)IF (@ReturnValue = 0)BEGIN

PRINT 'The Status for the Position: '

+ @Description

PRINT 'Budgeted Strength : ' + CONVERT( char(30), @budget)

PRINT 'Current Strength : ' + CONVERT (char(30), @CurrentStrength)

SELECT cRequisitionCode,vRegion, siNoOfVacancy FROM Requisition WHERE cPositionCode=@Pcode



Create the procedure in the database (Contd.) END

ELSE

PRINT 'No records for the given position code'

END Press F5 to execute



Check whether the procedure exists in the database

In the Query Analyzer window, type and execute the following statements:

sp_helptext prcGetPositionDetail

go

sp_helptext prcDisplayPositionStatus

go




Action:


prcDisplayPositionstatus '0002'

Press F5 to execute

Verify that the required output is displayed



The RECOMPILE Option

To generate an updated execution plan, you must recompile the stored procedure, each time you execute the stored procedure

Syntax

CREATE PROCEDURE proc_name [WITH RECOMPILE]

AS

sql_statements



The DROP PROCEDURE Statement

Is used to drop a stored procedure from the database

Syntax

DROP PROCEDURE proc_name

You cannot retrieve a procedure once it is dropped



Extended Stored Procedures

Allow you to create your own external routines in a programming language such as C

Are Dynamic-Link Libraries (DLLs) that SQL Server can dynamically load and execute



Summary

In this lesson, you learned that:

A stored procedure is a collection of various T-SQL statements that are stored under one name and executed as a single unit

A stored procedure can be created either in the Enterprise Manager or in the Query Analyzer window with the CREATE PROCEDURE statement

A stored procedure allows you to declare parameters, variables, and use T-SQL statements and programming logic

A stored procedure provides better performance, security, and accuracy, and reduces network congestion



Summary (Contd.)

The various types of stored procedures are:

User-defined

System

Temporary

Remote

Extended

A stored procedure accepts and returns data through the following:

Input parameters



Summary (Contd.)

Output parameters

Return statements

A stored procedure can be executed using the EXECUTE statement

A stored procedure can be altered using the ALTER PROCEDURE statement

A stored procedure can be viewed using the sp_help and the sp_helptext system procedures

To generate an updated execution plan, you must recompile the stored procedure each time you execute the stored procedure



Summary (Contd.)

To recompile a stored procedure automatically, you must create the stored procedure with a WITH RECOMPILE option

A stored procedure can be dropped using the DROP PROCEDURE statement

Extended stored procedures are Dynamic-Link Libraries (DLLs) that SQL Server can dynamically load and execute

sql xp 09

Technology

stored procedure thatwill

stored proceduresdraft

stored proceduresgetting

stored proceduresexecute

stored procedureniitsqllesson

stored proceduresjust

stored proceduresidentify

stored procedures9