A stored procedure (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 (UDF’s) but there are some 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 (in query window).

CREATE procedure Proce_Name
@Param1 int,
@Param2 varchar(50)
as
select * from TableName
where 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 (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.”

And if you want to Execute Stored procedure on Sql Server window then write.. 

Exec ProcedureName  [Param1 value], [ Param2 value]

Point 3: how to call the stored procedure in our application and passing the parameter.

Write these code on MVC.Controller 

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