Overview

Introduction

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

  • Working with stored procedures
  • Working with views

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

Exam Objectives

The following exam objectives are covered in this lab:

  • Demonstrate how to create a simple T-SQL stored procedure by using the CREATE -PROCEDURE statement and execute the stored procedure
  • Demonstrate how to create a procedure with input parameters and execute the procedure
  • Demonstrate how to create a procedure with wildcard parameters
  • Demonstrate how to create a procedure with output parameters
  • Demonstrate how to create a procedure with table-valued parameter
  • Demonstrate how to modify a procedure
  • Create a view by using a simple SELECT statement and query the view
  • Create a view using built-in functions in SELECT query and query the view
  • Create an indexed view by using Transact-SQL and query the indexed view
  • Modify a view

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

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

Exercise 2 - Working with Views

A view is a virtual table that is based on a SQL SELECT query. A view is an overall glance of the database and it refers one or more existing database tables or other views. You can create views only in the active database. You can query a maximum of 1024 columns in a single view.

In this exercise, you will learn to work with views.

Comprehensive Learning

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