---
title: "Store Procedure in SQL"  
description: "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 databa"  
author: "priyanka kushwaha"  
published: 2015-01-28  
updated: 2015-01-28  
canonical: https://www.mindstick.com/blog/735/store-procedure-in-sql  
category: ".net"  
tags: ["sql server"]  
reading_time: 2 minutes  

---

# Store Procedure in SQL

In this blog, I’m explaining about [store procedure](https://www.mindstick.com/forum/193/auto-increment-by-store-procedure) in .Net\

A store procedure is a group of SQL statements that has been created and stored in [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax). Store procedure will accept input [parameters](https://www.mindstick.com/articles/87/passing-parameters-in-c-sharp) so that a single procedure can be used over the network by serveral clients using different data. [Stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) will reduce network traffic and increase the performance.

[Advantages](https://www.mindstick.com/articles/12841/5-advantages-of-customer-portal-you-didn-t-know-about) of using store procedure

1. Stored procedure allows modular programming.

2. [Stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) provide [better security](https://answers.mindstick.com/qa/97144/which-is-the-better-security-measure-https-or-ssl) to your data.

\

##### Types of stored procedure

1. System stored procedure

2. [User Defined](https://www.mindstick.com/forum/34125/how-to-find-list-of-all-user-defined-tables-using-sql-server) 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)asbegin 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
```

##

---

Original Source: https://www.mindstick.com/blog/735/store-procedure-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.