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:
- 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.
- Return codes (integer value)
- 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.
The results will look something like this:
| empID | empName |
|---|
| 1000 | John Smith |
| 1001 | Fred White |
| 1002 | Jane Scott |
| 1003 | Samuel 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.
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources
