We’re nearing the end of the SQL103 series. We’ve designed tables, relationships, indexes and views. There’s plenty more to learn. Today we’re going to discuss designing stored procedures. When you start designing stored procedures you’re generally trying to build a wrapper around a collection of T-SQL statements that you can then hand off to developers. This wrapper buys you a couple things.

• You make sure that everyone does the same action in the same way. Rather than allowing everyone direct access to update a table, you can build in extra logic to check the data being entered. Then everyone who tries to update the table will go through those checks since you only gave them access to execute the procedure, instead of direct access to the table.
• Speaking of access, you can control who has the rights to complete a certain task.By only granting execute permission on a stored procedure, users won’t be able to directly update the table.
• You can provide a layer of abstraction. Consider the stored procedure that let’s you update a table. You can make changes to the table, without having to make changes to anyone’s code. Since they just pass parameters to the stored procedure it won’t matter that you changed the name of the fname column to FirstName. It’s abstracted!
• You have an additional layer of protection against SQL Injection Attacks. While switching from direct table access to stored procedures isn’t a cure-all for Injection Attacks, it’s a first step. If someone tries to inject select * from in as a parameter, and you’ve trapped your errors responsibly, the attacker won’t gain any insight into your system!

These are all good reasons for designing stored procedures. Now let’s dig into the design element.

So how do you design stored procedures?

Early in the design process you need to start asking, “how are you going to use this data?” You need to get an idea of how the end user is going to interact with it. Let’s consider our Customer table.

During the design process you should have asked that question.  Some of the answers you would have gotten back would have been: we want to be able to lookup customers by lastname.  We want to be able to update full names, we want to be able to update spouses.  Each of these three requests would start you down the path of developing stored procedures.

Since we already have a view for showing contact information, we might build the first stored procedure around the vContactCard view.  We would need to build a stored procedure that takes last name as a parameter, and returns the records as a result.  So we would have defined the following stored procedure.

USP_GetContactCardsWithLastName @LastName VARCHAR(50) — This stored procedure takes LastName, and returns all records from vContactCard where the LastName column equals the @LastName given.

We don’t have to actually build the stored procedure at this point, only define the inputs, the processing accomplished, and the outputs.

Think about the other two requests: updating names and updating spouses.  What definitions would you provide for these two requests?  Send in your suggestions in the comments, and we can discuss them further!  If you have any questions, send those in too!  I’m here to help.