Overview

Introduction

The Creating Stored Procedures with Parameters module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Working with stored procedures

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Six exam objectives are covered in this lab.

  • Demonstrate how to create a stored procedure using Transact-SQL statements
  • Demonstrate how to create a stored procedure which accepts input parameters with values which exactly match with the parameters using Transact-SQL statements (The stored procedure should implement transaction control logic)
  • Demonstrate how to create a stored procedure which accepts wildcard parameters using Transact-SQL statements
  • Demonstrate how to create a stored procedure which returns output parameters using Transact-SQL statements
  • Demonstrate how to create a stored procedure which inserts multiple rows into a table using table-valued parameters using Transact-SQL statements
  • Demonstrate how to get the status of a stored procedure using a return code using Transact-SQL statements

Exercise 1 - Working with Stored Procedures

A stored procedure is a group of Transact-SQL statements that are compiled into a single execution plan. Stored procedures in SQL Server are similar to procedures in any other programming languages. Stored procedures in SQL Server can perform the following tasks:

  • It accepts input parameters.
  • It returns output parameters to the calling procedure.
  • It performs operations in the database using Transact-SQL statements.
  • It returns a status value to indicate success or failure of the calling procedure.

The syntax to create a stored procedure is as follows:

CREATE PROCEDURE <ProcedureName, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <DatatypeForParam1, , int> = <DefaultValueForParam1, , 0>, <@Param2, sysname, @p2> <DatatypeForParam2, , int> = <DefaultValueFor_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO

In this exercise, you will learn to work with stored procedures.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.