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

Create a stored procedure in SQL Server using parameters to filter a list of venues.

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 Parameter Places.sql. You should find a query to return a list of venues.

A list of venues produced by a SQL Server query

A few of the results you should see if you run the query.

Add code to create a stored procedure called usp_Parameter_Places which returns the results of the query.

Add the following parameters to the procedure and modify the query so that it returns the indicated results:

Parameter name	Data type	What the query should return
@OpenedAfter	DATE	Venues opened after this date.
@MinCapacity	INT	Venues whose capacity is at least this much.
@MaxCost	FLOAT	Venues which cost no more than this amount.
@City	NVARCHAR(255)	Venues whose city name contains this text.

Create a new script, save it as Passing Parameter Places.sql and write an EXEC statement to test that the procedure works.

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

Venues opened after 1950-01-01, with a minimum capacity of 10000, a maximum cost of 100 million dollars and in cities with chester in the name.

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