Stored Procedures classification based on Input and Output Parameters in SQL Server
Last Updated :
18 Aug, 2021
Stored Procedure:
The stored procedure has the following key points as follows.
- 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.
- A stored procedure is a group of T-SQL (Transact SQL) statements.
- 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
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...