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 ==>	Relatively easy
Subject ==>	SQL training

Add parameters to a SQL stored procedure which returns a list of music albums.

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 Paralbumeters.sql. You'll find a script to create a stored procedure which returns a list of albums.

Execute the script to create the procedure.

A confirmation message in SQL Server Management Studio

The message we want to see.

Create a new script and write an EXEC statement to execute the stored procedure you have just created. Save this file as Passing Paralbumeters.sql.

A list of albums produced by a SQL Server stored procedure

The first few results you should see.

Return to the Paralbumeters script and add two parameters to the stored procedure as shown in the table below:

Parameter name	Data type
@HighChartPosition	TINYINT
@LowChartPosition	TINYINT

Add a WHERE clause to return albums whose peak chart position is between the values of the two parameters.

Execute the script to alter the procedure.

Return to the script containing the EXEC statement and try to run it.

A SQL Server error message caused by executing a stored procedure without passing values to required parameters.

We can't run the procedure until we assign values to the the parameters.

Modify the EXEC statement to pass a value to each of the parameters. You can do this with or without using named parameters:

--passing values to parameters in order

EXEC ProcedureName Value1, Value2

--passing values to named parameters

EXEC ProcedureName @Param1 = Value1, @Param2 = Value2

Run the script again to see the results of the procedure.

Results of a SQL Server stored procedure using parameters.

We've searched for albums with a chart position between 5 and 10.

Return to the Paralbumeters script and add two more parameters according to the table below:

Parameter name	Data type
@StartDate	DATE
@EndDate	DATE

Modify the WHERE clause to return albums released between the dates stored in the new parameters.

Execute the script to alter the procedure and then use the other script to test that it works.

A list of albums produced by a SQL Server stored procedure using parameters.

We've searched for albums released in the year 1992 and which made it into the top 10.

Save both scripts and then close them.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

SQL EXERCISES

SQL EXERCISES

Add parameters to a SQL stored procedure which returns a list of music albums.

Head office

London

Manchester