Home arrow SQL Server arrow Page 3 - SQL Server Stored Procedures 101

SQL Server Stored Procedures 101

Stored procedures can offer performance gains when used instead of regular queries. In this article Himanshu starts with the basics and gives us the "101" on stored procedures.

Author Info:
By: A.P.Rajshekhar
Rating: 4 stars4 stars4 stars4 stars4 stars / 178
June 03, 2002
  1. · SQL Server Stored Procedures 101
  2. · What are stored procedures?
  3. · Using parameters with procedures
  4. · Conclusion

print this article

SQL Server Stored Procedures 101 - Using parameters with procedures
(Page 3 of 4 )

Stored procedures are very powerful but to be most effective the procedure must be somewhat dynamic, which enables you, the developer, to pass in values to be considered during the functioning of the stored procedure. Here are some general guidelines for using parameters with stored procedures:
  • You can define one or more parameters in a procedure.
  • You use parameters as named storage locations just like you would use the parameters as variables in conventional programming languages, such as C and Visual Basic.
  • You precede the name of a parameter with an at symbol (@) to designate it as a parameter.
  • Parameter names are local to the procedure in which they're defined.
You can use parameters to pass information into a procedure from the line that executes the parameter. You place the parameters after the name of the procedure on a command line, with commas to separate the list of parameters if there is more than one. You use system data types to define the type of information to be expected as a parameter.

In example below, the procedure is defined with three input parameters. The defined input parameters appear within the procedure in the position of values in the VALUE clause of an INSERT statement. When the procedure is executed, three literal values are passed into the INSERT statement within the procedure as a parameter list. A SELECT statement is executed after the stored procedure is executed to verify that a new row was added through the procedure.

Creating a Stored Procedure with Input Parameters

create procedure proc4 (@p1 char(15), @p2 char(20), @p3 int) as
insert into Workers
values (@p1, @p2, @p3)

proc4 `Brat',Sales,3333

select * from Workers
where Badge=3333

Name Department Badge
--------- --------------- -----------
Brat Sales 3333

(1 row(s) affected).

Calling Stored Procedures from Your Application
On the application side, it can be quite cumbersome to have to specify each value on every call to the stored procedure, even in cases where the value is NULL. In those cases, the calling application can use named arguments to pass information to SQL Server and the stored procedure. For example, if your stored procedure allows up to three different arguments, name, address, and phone, you can call the routine as follows:

exec sp_routine @name="blah"

Displaying and Editing Procedures
You use the system procedure sp_helptext to list the definition of a procedure, and sp_help to display control information about a procedure. The system procedures sp_helptext and sp_help are used to list information about other database objects, such as tables, rules, and defaults, as well as stored procedures.

Making Changes and Dropping Stored Procedures
Two closely related tasks that you'll no doubt have to perform are making changes to existing stored procedures and removing no longer used stored procedures.

Changing an Existing Stored Procedure
Stored procedures cannot be modified in place, so you're forced to first drop the procedure, then create it again. Unfortunately, there is no ALTER statement that can be used to modify the contents of an existing procedure. This stems largely from the query plan that is created and from the fact that stored procedures are compiled after they are initiated.

Because the routines are compiled and the query plan relies on the compiled information, SQL Server uses a binary version of the stored procedure when it is executed. It would be difficult or impossible to convert from the binary representation of the stored procedure back to English to allow for edits. For this reason, it's imperative that you maintain a copy of your stored procedures in a location other than SQL Server. Although SQL Server can produce the code that was used to create the stored procedure, you should always maintain a backup copy.

You can pull the text associated with a stored procedure by using the sp_helptext system stored procedure. The syntax of sp_helptext is as follows:

sp_helptext procedure_name

Removing Existing Stored Procedures
You use the DROP PROCEDURE statement to drop a stored procedure that you've created. Multiple procedures can be dropped with a single DROP PROCEDURE statement by listing multiple procedures separated by commas after the keywords DROP PROCEDURE in the syntax:

DROP PROCEDURE procedure_name_1, ...,procedure_name_n

Example of stored procedure
Let's assume that we have the following table named Inventory:

A sample table

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:

@location varchar(10)
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command:

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory:

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 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.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials