---
title: "The Detailed concept of Stored Procedure in SQL Server"  
description: "The detailed concept of Stored Procedure in SQL ServerDefinition:Where a stored procedure is a special feature of a database management system (DBMS"  
author: "Amit Singh"  
published: 2010-11-27  
updated: 2020-07-13  
canonical: https://www.mindstick.com/articles/202/the-detailed-concept-of-stored-procedure-in-sql-server  
category: "asp.net"  
tags: ["asp.net"]  
reading_time: 4 minutes  

---

# The Detailed concept of Stored Procedure in SQL Server

## The detailed concept of Stored Procedure in SQL Server

#### Definition:

Where a stored procedure is a [special feature of a database](https://www.mindstick.com/articles/331/validating-fileupload-control-for-specific-file-type-and-required-field) management system (DBMS), And a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database because it is a pre-compiled form and so that it can be easily shared by a number of programs. If we can talk about the use of stored procedures it can be very helpful in controlling access to data (end-users may enter or change data but do not changed/write procedures), protected of data integrity ( the information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

#### Another Definition

The stored procedure is a set of SQL commands that have been compiled and stored on the database server. Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. And also, Stored procedures improve performance by reducing network traffic and CPU load.

This is used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command-line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.

##### Main Features:

- It is in compiled form.
- End-users may enter or change data but do not write procedures
- Information is entered in a consistent manner.
- Statements in a stored procedure only need to be written one time.
- It improves performance by reducing network traffic and CPU load.

It same as the user-defined function (UDF’s) but there are some difference between them

##### Main Benefits of the stored procedure:

**Precompiled Execution:** The stored procedure is stored in the database as a compiled form. So it is compiled only one time.

When a stored procedure is called may times so it gives fast and better results.

Reduced server and client traffic so it reducing the network traffic and CPU load.

It removes the SQL Injection problem. Etc.

How to use the Stored Procedure in SQL Server

##### Select query in Stored Procedure

**Step 1:** we write this statement on SQL Server (in query window).

```
CREATE procedure [dbo].[ProcedureName]@Parameter1 int,@Parameter2 varchar(50)asselect * from TableNamewhere TableFieldName1=@Parameter1 and TableFieldName2=@Parameter2
```

**Note :** where

1. The ProcedureName show the name of the procedure for example ProcCustomerInfo etc

2. The Parameter 1 and parameter 2 are declared the variable and these variables assigned at runtime.

3. The TableName indicates the Name of Table.

4. The TableFieldName1 and TableFieldName2 show the column name(field or attribute name)of TableName

**Step2:** Now Compile the stored procedure by pressing the f5 (Function key) button.

It shows the message on the message window as “*Command(s) completed successfully*.”;

**Step3:** how to call the stored procedure in our application and passing the parameter.

##### Write these code on aspx.cs file

```
//Connection Created here and making DataReader object drSqlCommand cmd = new SqlCommand("ProcedureName",con);//Procedure call in sql commandcmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Parameter1",UserValue1);//Set the parameter1 by user input value            cmd.Parameters.AddWithValue("@Parameter2", UserValue2); );//Set the parameter2 by user input value dr = cmd.ExecuteReader();//Execute the proceduredr.Read();//Read the first row//Access the value then close the DataReader and Connection
```

##### Insert Query in the stored procedure

**Step 1:** we write this statement on SQL Server (in query window).

```
CREATE PROCEDURE [dbo].[ProcedureName] (@Parameter1 varchar(50), @Parameter2 varchar(100)AS INSERT INTO TableName VALUES(@Parameter1,@Parameter2)
```

**Step2:** Now Compile the stored procedure by pressing the f5 (Function key) button. It shows the message on the message window as “Command(s) completed successfully.”;

**Step3**: how to call the stored procedure in our application and passing the parameter.

##### Write these code on aspx.cs file

```
SqlCommand command = new  SqlCommand("ProcedureName",con);command.CommandType = CommandType.StoredProcedure;          command.Parameters.Add("@Parameter1",  SqlDbType.VarChar).Value = UserInputValue1; //Assign the valuecommand.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the valuecommand.ExecuteNonQuery();//Execute the procedure return type integer
```

##### \
Update Query in the stored procedure

**Step 1:** we write this statement on SQL Server (in query window).

```
CREATE PROCEDURE [dbo].[ProcedureName] (@Parameter1 varchar(50), @Parameter2 varchar(100)AS UPDATE TableName SET TableFieldName1=@Parameter1 WHERE  TableFieldName2=@Parameter2
```

**Step2:** Now Compile the stored procedure by pressing the F5 (Function key) button. It shows the message on the message window as “*Command(s) completed successfully*.”;

**Step3:** how to call the stored procedure in our application and passing the parameter.

##### Write these code on aspx.cs file

```
SqlCommand command = new  SqlCommand("ProcedureName",con);command.CommandType = CommandType.StoredProcedure;          command.Parameters.Add("@Parameter1",  SqlDbType.VarChar).Value = UserInputValue1; //Assign the valuecommand.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the valuecommand.ExecuteNonQuery();//Execute the procedure return type integer
```

**Note:** Similarly create for delete as insert (created above)

---

Original Source: https://www.mindstick.com/articles/202/the-detailed-concept-of-stored-procedure-in-sql-server

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