Why do we need to use stored procedures?

A stored procedures is a pre-defined, reusable routine that is stored in a database. SQL Server compiles stored procedures, which makes them more efficient to use.

To execute stored procedures, you specify the name of the stored procedure in the first parameter of a SqlCommand constructor and then set the CommandType of the SqlCommand to StoredProcedure. You can also send parameters to a stored procedure by using SqlParameter objects, the same way it is done with SqlCommand objects that execute query strings. Once the SqlCommand object is constructed, you can use it just like any other SqlCommand object.

Introduction:

A Stored Procedure is a set of SQL statements which then execute by its declared name and perform defined task, if you want to execute some set of SQL queries again and again then stored procedure comes to picture, it Compiles for the first time then it execute faster whenever you run it while any SQL query compiles every time when you run it therefore Stored procedure is faster then SQL query its main purpose is to code reuseability and save compilation time.

Syntax:

CREATE PROCEDURE sp_name

AS

BEGIN

      SELECT * FROM employee

END

every stored procedure start with keyword CREATE PROCEDURE and then your stored procedure's name (this name will use to execute your stored procedure), now in between two keywords BEGIN and END your whole sql statement should be there,

How to Execute Stored Procedure:

once you created your SP then you can execute it by its name following by keyword exec, 

EXEC sp_name

Stored Procedure with Parameter:

Stored procedure can take parameter to perform its task, lets have a look at the following syntax

CREATE PROCEDURE sp_name @emp_name NVARCHAR(30)

AS

BEGIN

      SELECT * FROM employees WHERE emp_name = @emp_name

END

in first line after stored procedure name we have to declare a variable with its data type and in statement where we need this input we use the variable name

To run the above stored procedure

EXEC sp_name@emp_name = 'Jhon'

Stored procedures provide improved performance because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.

Stored Procedures are used for code reuseability.

We should use store procedures to improve performance of database because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.

I used it because it's more easy to define and return result set than using function.

Stored procedures is SQL is like a method/function in Java/C. It encompasses logic to be performed on the SQL data using SQL syntax. 

Stored Procs are very fast as they are run natively in the SQL database server. These are especially used when complex data has to be processed from tables to arrive at an output. 

The other reason is: 

If a SQL Query is executed from a programming language Java/.Net/C, the data if first fetched from the database and then computed using these programming languages, which is slow when compared to having this computation in the database server it self as part of stored procedure