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.
Create proc [dbo].[procInsertEmployee]
if((select count(*) from EmployeeDetail where FirstName=@FirstName and LastName=@LastName)>0)
if((select count(*) from EmployeeDetail where DateOfBirth=@DateOfBirth and FatherName=@FatherName)>0)
insert into EmployeeDetail(FirstName,LastName,DateOfBirth,FatherName,AddressLineFirst,AddressLineSecond,City,[State],Country,PinCode,PhoneNo,EmailAddress)