Best Answer · By JanBask MS SQL Server Expert
Rachit Gupta JanBask Expert
Answered on Feb 5, 2020
For the out parameters we need to mention the Out explicitly with the parameters, below is the sample and details for the same:
-- Create Stored Procedure
CREATE PROCEDURE usp_TestOutParameter
@p_First VARCHAR(50),
@p_Second VARCHAR(50) out
AS
begin
SELECT @p_Second =col2 from input_values where col1=@p_First
End
-- execute procedure via passing the data and getting the data in output values:
declare @V_Second VARCHAR(50)
exec usp_TestOutParameter 'One',@V_Second out
select @V_Second
Now using the out parameter as in/out both the ways:
create PROCEDURE usp_TestInOutParameter
@p_First VARCHAR(50),
@p_Second VARCHAR(50) out
AS
begin
select @p_Second ' Input_value'
SELECT @p_Second =col2+': got from table' from input_values where col1=@p_First and col2=@p_Second –-using the data from the input value
end
declare @V_Second VARCHAR(50)='Two'
exec usp_TestInOutParameter 'One',@V_Second out
select @V_Second output_value
