Create Stored Procedures

Introduction

The Create Stored Procedures module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

Learning Outcomes

In this module, you will complete the following exercises:

• Exercise 1 - Insert, Update and Delete Data in tables using Stored Procedure

After completing this lab, you will be able to:

• Use stored procedures to manipulate data stored in tables

Exam Objectives

The following exam objective is covered in this lab:

• 2.4 Create stored procedures and functions

Lab Duration

It will take approximately 20 minutes to complete this lab.

Exercise 1 - Insert, Update and Delete Data in tables using Stored Procedure

Instead of running multiple queries one after another, you can create a procedure. A procedure is also useful when you need to run the same set of queries often. In these cases, you can write the queries once in a procedure and then just call the procedure when required. This reduces the time required to write the queries repeatedly. Another advantage is that, the stored procedures are pre-compiled by the database engine and are stored within the database itself. Therefore, the stored procedures do not need to be sent to the server for compilation every time. This helps reduce the time required to run the queries and prevent network congestion.

To create a stored procedure, you should use the Create Procedure statement. You can then embed the required SQL statements in a begin-end block within the Create Procedure statement. You can also use input and output parameters when creating a stored procedure. An input parameter indicates that the program that calls the stored procedure must send the required values to the procedure for it to execute. An output parameter, on the other hand, indicates that the stored procedure will send values to the calling program. By default, all parameters are considered as input parameters. To designate a parameter as an output parameter, you must specify the keyword output with the parameter name.

In this exercise, you will perform the tasks of inserting, updating, and deleting data in the dbo.Employee table that you created in the previous exercise through a stored procedure. You will first create stored procedures using Transact SQL to insert and delete data in the dbo.Employee table. You will then create a stored procedure using SQL Server Management Studio to delete the specified data from the dbo.Employee table.

Learning Outcomes

After completing this exercise, you will be able to:

• Use stored procedures to manipulate data stored in tables