Stored Procedures
What Stored Procedure means ?
A Stored procedure is a database object that contains one or more SQL statements. In this article you will get an idea on how to create and use stored procedures and also highlighted on how to use stored procedure.
The first time a stored procedure is executed; each SQL statement it contains is compiled and executed to create an execution plan. Then procedure is stored in compiled form with in the database. For each subsequent execution, the SQL statements are executed without compilation, because they are precompiled. This makes the execution of stored procedure faster than the execution of an equivalent SQL script.
To execute a stored procedure you can use EXEC statement.
CREATE PROC spGetShashi
AS
SELECT * FROM SHASHI
When you run this script in Pubs database you will get the following message in Query Analyzer.
The Command(s) completed successfully.
Now you are ready to call/execute this procedure from Query Analyzer.
EXEC spGetShashi
This stored procedure creates a result set and returns to client.
You can call a stored procedure from within another stored procedure. You can even call a stored procedure from within itself. This technique is called a recursive call in programming. One of the advantages of using stored procedures is that application programmers and end users don't need to know the structure of the database or how to code SQL. Another advantage of it is they can restrict and control access to a database.
Now days every one is familiar with SQL Injection Attack I think stored are the way this can be prevented from this malicious attack.
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (Procedure name) or EXECUTE procedure((Procedure name)
1.Manage, control and validate data
2.It can also be used for access mechanisms
3.Large queries can be avoided
4.Reduces network traffic since they need not be recompiled
5.Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored 6.procedures increases reusability of code
Permissions can be granted for stored procedures. Hence, increases security.