A stored procedure is nothing more than prepared SQL Query that you save
so you can reuse the Query over and over again. So if you think about a query
that you write over and over again, instead of having to write that query each the time you would save it as a stored procedure and then just call the stored
procedure to execute the SQL code that you saved as part of the stored
procedure. Stored procedures are a batch of SQL statements that can be executed
in a couple of ways. Most major DBMS support stored procedures; however, not
all do. You will need to verify with your particular DBMS help documentation
for specifics
A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input
data. A stored procedure will reduce network traffic and increase the
performance
Advantages of stored procedures
- A stored procedure allows
modular programming.
- A stored procedure allows
for faster execution.
- A stored procedure can
reduce network traffic.
- Stored procedures provide
better security to your data
Example of the stored procedure :
Step 1-Create simple stored procedure in SQL server
|
create
procedure proc_getAllData
as
begin
select
* from temptable
end
|
Step 2-Execute stored procedure in SQL server
|
exec
proc_getAllData
|
Note: If you want to delete the stored procedure then use drop command
to drop the stored procedure
|
drop
procedure proc_getAllData
|
Types of Stored Procedure
SQL Server we have different types of stored procedures:
- System stored procedures
- User-defined stored
procedures
- Extended stored Procedures
System stored procedures
Stored procedures are stored in the master database and these start with
a sp_ prefix. These procedures can be used to perform a variety of tasks to
support SQL Server functions.
Example: sp_helptext [StoredProcedure_Name]
(To get the text of stored procedure )
User-defined stored procedures
User-defined stored procedures are usually stored in a user database and
are typically designed to complete the tasks in the user database.
Extended stored Procedures
Extended stored procedures are the procedures that call functions from
DLL files. Nowadays, extended stored procedures are depreciated for the reason
it would be better to avoid using extended stored procedures.
