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
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.
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.