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 :
