This page has 0 threads | Add post

Exercises

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

Software ==>	SQL (203 exercises)
Topic ==>	Testing conditions (3 exercises)
Level ==>	Average difficulty
Subject ==>	SQL training

Use IF statements in SQL to validate the values passed in to parameters of a stored procedure.

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 Vali Date Parameters.sql file in the folder shown above and execute the script to create a stored procedure.

Create a new script to test the procedure, passing in any dates to the procedure's parameters.

Executing a stored procedure in SQL Server Management Studio

This example returns albums released in 1992.

If we use too late a date for the @StartDate parameter or too early a date for the @EndDate parameter then we may not return any results. Your job is to add code to the stored procedure to validate the values passed in to the parameters.

In the stored procedure, use a variable to capture the maximum date in the Release_date column of the Album table.

Use an IF statement to check if the value passed in to the @StartDate parameter is after this date and shows a message then exits the procedure if so.