Lab 6

# CS327e

# Lab 6

Fall 2004

Due: 2004-11-15 Monday 8.30 a.m. in class and in your group account.

PURPOSE:

The purpose of this lab is to learn about Stored Procedures in SQL Server. This is a group submission. In other words, your group needs to submit 1 copy, and you are free and encouraged to collaborate within your group.

DESCRIPTION/IMPLEMENTATION:

Consider the SQL Server Catalog. The catalog comprises of various built-in “system tables” with the prefix “sys”. In order for users to query these system catalog tables, there are built-in views in SQL Server called “Information Schema”.

The description of these system tables can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

The description of these views can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp

Write a procedure that takes in as input, an object name.

The procedure is supposed to verify (by querying the information schema) whether the input object name is

· An existing table name,

· An existing view name,

· Or neither

The procedure then prints this fact.

In the case where it is a table or a view name, the procedure then prints the names of all the columns (in alphabetical order) belonging to that view/table name.

Test this procedure using some tables/views that you might have created in your group account.

The default value for the input parameter is NULL.

NOTE: All character comparisons need to be case-insensitive (HINT: Use UPPER or LOWER built-in function of SQL Server).

NOTE: For non-NULL input values, you need to check if they exist. If not, return a user-friendly message containing the name that doesn’t exist.

NOTE: Provide adequate documentation inside the procedure. State your assumptions clearly.

Submission:

Print a Word copy of the CREATE PROCEDURE and submit in class. In addition, the TA will be logging into your group account, and verifying various use cases as inputs.

Last Updated: 11/4/2004 11:17:22 AM