What is stored procedure?

In relational database system, stored procedure is precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers implement it with the specified a stored procedure name and parameters (if the stored procedure with parameters). Storage process is a process which is controlled by the flow and the statements of writing SQL. This process is stored in the database server by optimized and the compiled. Applications just need call it in used. In ORACLE, the number of processes can be packed together to constitute a package of program.

Stored procedure is a procedure, which is edit with Transact-SQL languag(it is provided by SQL Server). Tranact-SQL language is a specifical language of SQL Server database, and it is provided for designing database application. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Database stored procedure is an important target, and stored procedure should be used in any well-designed database application:
SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly;
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;
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;
Stored procedures can enhanced security controls, you can grant users permission to execute a stored procedure independently of underlying table permissions.

Readers also visit these:

Related Questions: