A stored procedure is an executable object stored in a database. Calling a stored procedure is similar to invoking a SQL command. Using stored procedures on the data source (instead of executing or preparing a statement in the client application) can provide several advantages, including higher performance, reduced network overhead, and improved consistency and accuracy.
A stored procedure can have any number of (including zero) input or output parameters and can pass a return value. You can either hard code parameter values as specific data values or use a parameter marker (a question mark '?'), as shown below.
This topic covers regular stored procedures; extended stored procedures are covered in the general database topics:
Stored procedureВ В В One or more SQL statements that have been precompiled into a single executable procedure.
Extended stored procedureВ В В C or C++ DLLs written to the SQLВ Server Open Data Services API for extended stored procedures. The Open Data Services API extends the capabilities of stored procedures to include C or C++ code.
The OLE DB provider for SQLВ Server (SQLOLEDB) supports the following mechanisms that stored procedures use to return data:
Every SELECT statement in the procedure generates a result set.
The procedure can return data through output parameters.
The procedure can have an integer return code.
You cannot use stored procedures with the OLE DB provider for Jet because that provider does not support stored procedures; only constants are allowed in query strings.