Did you find this useful?
Socialize it today.


Stored Procedures

Thursday, January 05, 2012

tags SQL

A SQL stored procedure is a group of Transact-SQL statements compiled into a single execution plan. SQL stored procedures return data in various ways, such as:

  1. Output parameters, which generally return data such as an integer or character value, but can even return a cursor variable. This is the most common return.
  2. Return codes (integer value)
  3. A result set for each SELECT statement contained in the stored procedure or other stored procedures.

Stored procedures assist in creating a implementation of logic across various client applications. The SQL statements needed to perform a common task can be packaged as a stored procedure, and each application needing to perform that task can then use the existing stored procedure without having to build more logic. Another benefit is that applications do not need to send all of the SQL statements when communicating with the database. They only have to EXECUTE a procedure by name and provide the values of the parameters. This also ensures that users are shielded from needing to know the details of the tables in the database.

Here is an example of how to create a stored procedure.

Syntax



USE databaseName;
GO
CREATE PROCEDURE procedureName
AS
SELECT columnName FROM tableName;
GO


Example



USE ITGDB;
GO
CREATE PROCEDURE procEmpList
AS
SELECT empID, empName FROM Employees;
GO

Once the stored procedure is created, you can simply execute this procedure by using the following syntax.

EXEC procEmpList

The results will look something like this:

empIDempName
1000John Smith
1001Fred White
1002Jane Scott
1003Samuel Williams

This is, of course, a very simple example. The power of the stored procedure is easily seen when you include the use of parameters in your stored procedure.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook