Hi Friends,
Thanks for visiting my posts,
In this Post , i would like to post about stored procedure in SQL Server.
1.What is stored procedure ?
2. How to Create a stored procedure ?
3. How to Execute a stored procedure ?
stored procedure
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.
How to Create a stored procedure ?
1. Open Run-->SSMS( SQL Management Studio will get open).
2. Select the Database and Select the Table on which you want to write the
SP(store Procedure).
3. In our case , Database is SAMPLED and Table name is EmpData.
4. Put Some data into the table.
5. Select New Query from the ribbon.
7 . After creating the Stored Procedure click on "Execute" in the Ribbon.
How to execute a stored procedure ?
1. EXEC SP_Name and execute , you will fetch the data from table
This is how we create a stored procedure and
executes a stored procedure.
Advantages
of using stored procedures
a)
a)
Stored procedure allows modular programming.
You can create the
procedure once, store it in the database, and call it any number of times in
your program.
b)
b)
Stored Procedure allows faster execution.
If the operation
requires a large amount of SQL code is performed repetitively, stored
procedures can be faster. They are parsed and optimized when they are first
executed, and a compiled version of the stored procedure remains in memory
cache for later use. This means the stored procedure does not need to be
reparsed and reoptimized with each use resulting in much faster execution
times.
c)
c)
Stored Procedure can reduce network traffic.
An operation
requiring hundreds of lines of Transact-SQL code can be performed through a
single statement that executes the code in a procedure, rather than by sending
hundreds of lines of code over the network.
d)
d)
Stored procedures provide better security to your data
Users can be granted
permission to execute a stored procedure even if they do not have permission to
execute the procedure's statements directly.
In SQL we are having
different types of stored procedures are there
a)
System
Stored Procedures
b)
User
Defined Stored procedures
c)
Extended
Stored Procedures
System Stored Procedures:
System stored
procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be
used to perform variety of tasks to support sql server functions for external
application calls in the system tables
Ex: sp_helptext
[StoredProcedure_Name]
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. While coding these
procedures don’t use sp_ prefix because if we use the sp_ prefix
first it will check master database then it comes to user defined database
Extended Stored Procedures:
Extended stored
procedures are the procedures that call functions from DLL files. Now a day’s
extended stored procedures are depreciated for that reason it would be better
to avoid using of Extended Stored procedures.
Hope this blog helps you.....
Hope this blog helps you.....
