STORED PROCEDURE is a set of user defined code is to be stored for further use for multiple times. If we have SQL query which is need to write multiple times to get result, Instead of writing that query multiple times store that query in stored procedure whenever we want to use the query simply call that stored procedure without writing that query multiple times. we can pass parameters also in stored procedure based on that parameters we get output in result set.

CREATE PROCEDURE procedure_name
AS 
sql_statement
GO; 

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Creating stored procedure on existing table.

Requirement – Creating selectallemployees as stored procedure on employee_details table. The query was as follows –

CREATE PROCEDURE selectallemployees
AS
SELECT * FROM employee_details
GO; 

By executing above query, we can create selectallemployees stored procedure on employee_details. When ever we want to fetch all details from employee_details table instead of writing that query multiple times just call that selectallemployees stored procedure like as shown in below –

EXEC selectallemployees ;  

By executing above we can get all details of employees details from employee_details table.

Scenario – Creating stored procedure on existing table with single parameter.

Requirement – Creating stored procedure selectallemployees as stored procedure with single parameter on employee_details table. The query was as follows –

CREATE PROCEDURE selectallemployees @designation VARCHAR(20)
AS
SELECT * FROM employee_details WHERE designation = @designation
GO; 

By executing above query we can create selectallemployees stored procedure with designation on employee_details. If we want to fetch Director designation employee details we can use above stored procedure as shown in below –

EXEC selectallemployees @designation = ' Director ' ; 

By executing above query we can get details from employee_details table whose designation is Director. The output was as follows –

Scenario – Creating stored procedure on existing table with multiple parameters.

Requirement – Creating stored procedure selectallemployees as stored procedure with single parameter on employee_details table. The query was as follows –

CREATE PROCEDURE selectallemployees @designation VARCHAR(20),
@dept_id int
AS
SELECT * FROM employee_details WHERE designation = @designation
and dept_id = @dept_id 
GO; 

By executing above query we can create selectallemployees stored procedure with designation, dept_id on employee_details. If we want to fetch Director designation and dept_id is 1000 employee details we can use above stored procedure as shown in below –

EXEC selectallemployees @designation=' Director ' AND dept_id=1000 ; 

By executing above query we can get details from employee_details table whose designation is Director. The output was as follows –