What is Stored Procedure In SQL Server | What Are The Advantages Of Using Stored Procedure | How To Create Stored Procedures in SQL Server

• Stored Procedure  is the collection of (SQL) Structure Query Language Statements with an assigned name that is stored in Data Base.
• Stored procedures in SQL Server are Modular(It is easy to troubleshoot the Stored Procedure than embedded Query)
• It executes only one time at server so performance increase  
• It reduces Network Traffic.
• SP provides a good Security to our Data Base
• One Important Thing about SP is" You create a Stored Procedure and store in DB and Call number of times"

Disadvantages Stored Procedure

• It Increases the server side Process By Using Stored Procedures
• Don't us SP in SQL Server in  all of your in Business Logic Because ISV Applications (Independent Software Vendor) support only multiple RDBMS (Relational Data Base Management System) so no need to maintain the separate SP for each system.

Types Of Stored Procedures

In Structure Query Language SQL has 3 different types of Stored Procedures.They are

1. System Defined SP 
2. User Defined SP
3. Extended SP

System Defined Stored Procedures :

• it start with the a prefix is  "_sp"
• this SP stored in Master Data Base
• Ex: sp_Hello[Stored Procedure_Name]

User Defined Stored Procedures :

•  It Usually stored in Data Base
• While coding please dont use the prefix "_sp". If you use the prefix then first it check the Master DB so it comes to user defined DB.

Extended Stored Procedures :

 Extended SP are procedure and calls the function from DDL files.

mow a days it is better to avoid using the Extended SP

Big Question: When To Use SP

SP are well for 2 Tier Architecture Projects But The Trend slowly shifted 3-Tier and N-Tier Environment. In that scenario Business Logic always handled in some Middle Tier. In that time ,we would  like to restrict SP on basic Data Related tasks such as DELETE, UPDATE And SELECT.

How To Create Stored Procedure

First create the table with the required coloms.

CREATE TABLE emp(emp_Id int,emp_Name varchar(30),emp_Salary varchar(30))

 execute it 

now you create the Query and don't use the system defined SP please  use User Defined SP.

CREATE PROCEDURE  EMPLOYEESP(

@EMP_id inT,

@EMP_NAME varchar(30),

@EMP_SALARY varchar(30)

)

AS

BEGIN

select *from emp(emp_ID,emp_Name,emp_Salary)Values(@EMP_id inT,@EMP_NAME varchar(30),@EMP_SALARY varchar(30))

end

Now execute the above query 

after that you can use in any number of times in your application because SP are creating once and reusing in any number of times.

If You Enjoyed this  Post,Please support the blog it's Always FREE

The top topics
View state in ASP.NET
Client side State Management In ASP.NET
Indroduction to ADO.NET

Introduction ADO.NET
What is Cookies