Home > DeveloperSection > Blogs > Store Procedure in SQL

Store Procedure in SQL


.NET SQL Server 
Ratings:
0 Comment(s)
 564  View(s)
Rate this:

Store Procedure in SQL

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

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick