This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SQL (203 exercises)
Topic ==>	Parameters and return values (17 exercises)
Level ==>	Average difficulty
Subject ==>	SQL training

Use optional parameters in SQL Server stored procedures to return a list of concerts.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

If you don't have a copy of the Music_01 database, follow the instructions at the following link to create it: www.wiseowl.co.uk/sql/exercises/standard/using-sql-server/5257/

Open the file called The Show Must Go On.sql. You'll find a script to create a stored procedure which returns a list of shows, including information on whether a show was cancelled.

Results of a SQL query showing a list of concerts.

The Cancelled column contains 1 if a show was cancelled, and 0 if it was not.

Add a parameter called @CancelledOrNot to the procedure using the BIT data type. Add a WHERE clause to the query to check if the value of the Cancelled column equals the value of the parameter.

Create a new script to test that the procedure works.

A list of cancelled concerts produced by a SQL Server stored procedure with a parameter.

The show must go on... except when it doesn't.

It's annoying to have to provide a value to the parameter each time we execute the script. Assign a default value of 0 to the parameter so that the procedure returns the non-cancelled shows if you don't pass any value to the parameter.

Results of a SQL Server stored procedure using an optional parameter.

Test that the procedure returns results when you don't pass a value to the parameter.

Ideally, if we don't pass a value to the parameter, we should see both the cancelled and non-cancelled shows. Assign a default value of NULL to the parameter. Modify the WHERE clause so that a row is returned if either:

The Cancelled column is equal to the value of the @CancelledOrNot parameter; or
The @CancelledOrNot parameter is null.

Test that the procedure returns the correct type of show whether or not you pass a value to the parameter.

Add two optional date parameters called @StartDate and @EndDate which allow you to search for shows which happened between any two dates.

Test that you can pass any combination of values to the stored procedure.