Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  What concerns should you have about inserting data from a procedure with INSERT … EXECUTE ? How can you avoid data integrity problems with this method?

2.  In what part(s) of the system should you trap errors when you are using stored procedures?

3.  What three things must you do to make a procedure a system procedure?

4.  How do you “fully qualify” a remote procedure name?

Exercises

1.  Write a procedure that updates the price of a title, passing the price of the title and its title_id. The update must verify that the book has not been published (compare pubdate to today’s date) and the new price is more than $5.00. If the update does not complete successfully (encounters an error or no rows are updated), return an error. Make certain that the procedure holds a lock during the update. Test the procedure.

2.  Write a system procedure that displays the usernames and login names of all the users of the current database. (Hint: usernames are in sysusers; login names are in master..syslogins.)