Creating Stored Procedures with Parameters

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 <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_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.