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.
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.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.