SQL Server – Stored Procedure returning output parameter

Let’s see a simple example on How we can return a parameter value using Stored Procedures in SQL server. SQL Stored Procedure Output Parameter Syntax, Sample and Execution Script.

Below is the sql script with Syntax and Usage for Stored Procedure with Output Parameter Value.

Database Script of SQL Server :

create table Producttbl (
Id int identity (1,1) not null,
ProName nvarchar(50) not null,
ProDesc nvarchar(50) not null,
OnDate nvarchar(50) not null
)
truncate table Producttbl
insert into Producttbl (ProName,ProDesc,OnDate) values ('Samsung J7', 'Gamers targeted mobile from Samsung', '2015-10-17 19:56:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Samsung J5', 'Gamers targeted mobile from Samsung', '2015-10-17 19:56:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Xiaomi Redmi 2', 'Best Entry level smartphone from Xiaomi', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Apple iPhone 6s', 'Flagship device from APPLE', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Apple iPhone 6s Plus', 'Flagship device from APPLE', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Xiaomi Redmi Note 2', 'Best Entry level smartphone from Xiaomi', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Lenovo A6000', 'Best Entry level smartphone from Lenovo', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Motorola MOTO X Pure Edition', 'Best Android device right now!!', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Motorola MOTO E 2gen', 'Budget device from Motorola', '2015-10-17 19:59:00.000')
insert into Producttbl (ProName,ProDesc,OnDate) values ('Motorola MOTO E 2gen 4G', 'Budget device from Motorola', '2015-10-17 19:59:00.000')
select * from Producttbl

SQL Stored Procedure with output Parameter Value :

create procedure sp_ProductName
@ProductName nvarchar(50),
@ProductId int output
as
Select @ProductId=Id
from Producttbl where ProName=@ProductName;
return @ProductId

Syntax : SQL Stored Procedure with output Parameter Value :

Create Procedure StoredProcedureName
@ReturnParameterName ParameterDataType output // keyword output is important
as
Select @ReturnParameterName=ColumnName
from YourTableName where YourCondition // Condition is not important
return @ReturnParameterName // case sensitive

Executing our Stored Procedure with return Parameter value:

Syntax :

declare @DisplayParameterName datatype;
execute StoredProcedureName @ReturningParameterName = @DisplayParameterName output;
Select @DisplayParameterName as 'DisplayName'

Usage :

declare @Id int;
execute sp_ProductName 'Samsung J7',@ProductId=@Id output;
select @Id as 'Product Id'

Output :