In this blog, I’m explaining about store procedure  in .Net

A store procedure is a group of SQL statements that has been created and stored in database. Store procedure will accept input parameters so that a single procedure can be used over the network by  serveral  clients using different data. Stored procedure will reduce network traffic and increase the performance.

Advantages of using store procedure

1.      Stored procedure allows modular programming.

2.      Stored procedures provide better security to your data.

Types of stored procedure

1. System stored procedure

2. User Defined stored procedure

3. Extended stored procedure

System Stored Procedure

System stored procedures are stored   in the master database and these are starts with a sp_prefix.

 Example: sp_helptext storedProcedureName

User Defined Stored Procedure

User Defined Stored Procedures are usually  stored in a  user database and are typically designed to completed the task in the  user  database

Extended stored procedure

Extended stored procedure are registered in the master database, and the system administrator (SA) maintains control over their usage and registration.

Example

Create  proc [dbo].[procInsertEmployee]
(
            @FirstName nvarchar(50)=NULL,
            @LastName nvarchar(50)=NULL,
            @DateOfBirth date=NULL,
            @FatherName nvarchar(50)=NULL,
            @AddressLineFirst nvarchar(max)=NULL,
            @AddressLineSecond nvarchar(max)=NULL,
            @City nvarchar(50)=NULL,
            @State nvarchar(50)=NULL,
            @Country nvarchar(50)=NULL,
            @PinCode nvarchar(50)=NULL,
            @PhoneNo nvarchar(50)=NULL,
            @EmailAddress nvarchar(50)=NULL)
as
begin
 
if((select count(*) from EmployeeDetail where FirstName=@FirstName and LastName=@LastName)>0)
select  'True'
if((select count(*) from EmployeeDetail where DateOfBirth=@DateOfBirth and FatherName=@FatherName)>0)
select 'False'
else
    insert into EmployeeDetail(FirstName,LastName,DateOfBirth,FatherName,AddressLineFirst,AddressLineSecond,City,[State],Country,PinCode,PhoneNo,EmailAddress)
values(@FirstName,@LastName,@DateOfBirth,@FatherName,@AddressLineFirst,@AddressLineSecond,@City,@State,@Country,@PinCode,@PhoneNo,@EmailAddress)
select @@identity
 
end