Stored Procedures in SQL Server
Benefits
• Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Structure
Example
This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.
We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our
EXECUTE sp_GetInventory 'FL'
The
EXECUTE sp_GetInventory 'NY'
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.
Create an ASP SQL Stored Procedure
The useful thing about stored procedures is that they separate out the database activity from the displaying. This keeps your ASP pages very simple and makes maintenance a lot easier.
Here I'll introduce you to the basics of using a stored procedure and passing a parameter to it.
CREATING THE STORED PROCEDURE
Let's create a procedure called getproducts
Here we have created a variable called
@product_id
and passed it to our SQL select statement. All variables have the @ sign before them in SQL language.
Now, from within your ASP page, you can call this procedure in a number of ways.
As the procedure in this case is a select statement, you will most likely want to receive the results into a recordset:
If however the procedure was an update statment - for example,
update products set delivered=1 where product_id=@product_id
then you might want simply to execute the procedure as follows: