SQL Stored Procedures

Yesterday I talked about SQL Functions and their benefits.  Today I wanted to talk about SQL Stored Procedure.  Much like a SQL Function, a Stored Procedure takes in parameters and performs certain actions.  A Stored Procedure can do more, though, which would include the following:

• Update data within the database
• Return more than one value
• Call (run) other stored procedures
• Return status flag on whether or not the process completed successfully or failed

So why would someone have need for a SQL Stored Procedure?  Let's take the situation, for example, that you are an online company that takes sales orders, much like Amazon.com or eBay.  Every time someone completes an order, you have two choices.

1. Have the web interface (website) run certain insert statements into your database so the order is tracked.
2. Have the web interface (website) run one or multiple Stored Procedures to insert the order into your database

Both options are perfectly viable, but for option #1, anyone who understands HTML code can easily capture your statements and use it to hack into your database.

The second option, on the other hand, not only obfuscates (hides) data about your database, but you can also more easily control what happens if an error occurs at any moment in the insert.  This could include trying to run the stored procedure again if it fails, or show a pop-up window to a customer on failure.  These stored procedures can also be set up to fulfill your company's business rules.  And then, if for whatever reason your business rules change, you just have to update your stored procedure, instead of your website.

The syntax to create a stored procedure looks like the following:


CREATE PROCEDURE  
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = , 
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

If I wanted to create a stored procedure that returned my name, it would look like this:

CREATE PROCEDURE myName

AS

BEGIN

SELECT 'Bill Lantz'

END

Of course, stored procedure can be much more complicated than this.  For instance, if you wanted to allow someone to provide a customer first name, last name, and phone number and then insert it into your customer table, it would look something like this:


CREATE PROCEDURE insertCustomer
(
@LastName varchar(100),
@FistName varchar(100),
@PhoneNumber varchar(10)
)
AS
BEGIN

Insert into Customer
Values (@LastName, @FirstName, @PhoneNumber)

END

How do you think SQL Stored Procedures would work at your organization?