Stored Procedures classification based on Input and Output Parameters in SQL Server

Stored Procedure:

The stored procedure has the following key points as follows.

1. It is a collection of SQL statements such as if you want to write and read data from a database then you can use create a statement for write and select statement for reading the data from database and SQL command logic for the same, which is compiled and stored on the database.
2. A stored procedure is a group of T-SQL (Transact SQL) statements.
3. If you have a situation, where you can write the same query over and over again, you can save that specific query as a stored procedure and call it just by its name.

Classification of Stored Procedure

Create Stored Procedure without Parameter :

 You can use the following stored procedure given below to create the stored procedure without a parameter.

create procedure sp_get_empno
as  
begin  
select * from emp where ename='WARD'  
end
exec sp_get_empno

Create a Stored Procedure with Output Parameter :

 You can use the following stored procedure given below to create the stored procedure with an output parameter.

declare @empId int
exec GetEmployeeID (@empId int out)
create procedure sp_get_empid(@name varchar(10) out)
as  
begin  
select id from emp where ename="Sam"
end
exec sp_get_empid @empID

Create a Stored Procedure with Input Parameter :

 You can use the following stored procedure given below to create the stored procedure with an Input parameter.

USE Db1
GO
CREATE PROCEDURE dbo.GetEmployeeID(@Email varchar(30))
AS
SELECT *  FROM employeeDetails WHERE email= @Email
GO

Create a Stored Procedure with both input and Output Parameter :

 You can use the following stored procedure given below to create the stored procedure with both an input and output parameter.

create procedure sp_get_empname(@name varchar(10) out, @id int)
as
begin
select ename from emp where empno=@id
end
declare @en varchar(10)
exec sp_get_empname @en,7521
print @en