---
title: "Stored Procedure"  
description: "In this article i am going to show how i can make stored procedure and how it is used in MVC project"  
author: "Niraj Kumar Mishra"  
published: 2017-08-21  
updated: 2017-12-29  
canonical: https://www.mindstick.com/articles/12539/stored-procedure  
category: "mssql server"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Stored Procedure

A [stored procedure](https://www.mindstick.com/articles/36/stored-procedure-in-microsoft-sql-server) (sp) is a set of SQL commands that has been compiled and stored on the database server. Once the stored procedure is store on database server then user are use that stored procedure many times where they need.\

By using Stored procedure user can do INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters operation.

##### Main Features of sp are:

· It is in compiled form.

· End-users may enter or change data but do not write procedures

· Statements in a stored procedure only need to be written one time.

· It improves performance of CPU by reducing network traffic and CPU load.

It same as [user defined function](https://www.mindstick.com/blog/11188/user-define-function-in-sql-server) (UDF’s) but there are some [difference between them](https://www.mindstick.com/forum/23342/what-are-jsf-servlet-and-jsp-and-what-is-difference-between-them)

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

**What is Precompiled Execution:** Stored procedure is stored in database as a compiled form. So it is compiled only one times. And user are use that sp in many times.

##### How we can use the Stored Procedure in SQL Server

##### Query in Stored Procedure

**Point 1:** we write this statement on [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) (in query window).

```
CREATE procedure Proce_Name@Param1 int,@Param2 varchar(50)asselect * from TableNamewhere TableFieldName1=@Param1 and TableFieldName2=@Param2;
```

Note:\

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

2. Param1 and param2 is declared variable and those variables assigned at runtime.

3. TableName indicates the Name of Table.

4. TableFieldName1 and TableFieldName2 show the [column name](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-sql-query) (field or attribute name) of TableName.

\

**Point 2:** You compiled own stored procedure by using F5 function key on the keyboard. It show the message on message window as **“Command(s) completed successfully.”**

Andif you want to [Execute Stored](https://www.mindstick.com/interview/2542/how-can-we-execute-stored-procedures-and-functions) procedure on Sql Server window then write..

```
Exec ProcedureName  [Param1 value], [ Param2 value]
```

\

**Point 3:** how to call the stored procedure in our [application](https://www.mindstick.com/articles/12824/calculator-application-in-android) and passing the parameter.

Write these code on MVC.Controller

```
context.Database.SqlQuery<myEntityType>("exec ProcedureName ", @Parameter1,@Parameter2,new SqlParameter("Parameter1", variable1),new SqlParameter("Parameter2",variable2));
```

---

Original Source: https://www.mindstick.com/articles/12539/stored-procedure

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