SQL SERVER – Input and Output Parameter For Dynamic SQL

An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter.

A parameter whose value is given into a stored procedure/function module is known as an input parameter. An IN parameter’s value is fixed; it cannot be modified or reassigned within the module.

A parameter whose value is sent out of the stored procedure/function module and back to the calling PL/SQL block is known as an output parameter. A variable, not a constant, must be used as an output parameter. It can only be found on the left-hand side of a module assignment. Outside of the module’s body, you can’t provide a default value for an OUT parameter. 

Sometimes SQL DBAs end up in a situation where they don’t remember the syntax of any query and do a search on the internet. Below are three examples. Each example will contain the dynamic SQL and Input, Output, and Input/output parameters together.

To define input or output parameters use the keyword “DECLARE” followed by the symbol “@” name of variable “datatype”.

Example: 

DECLARE @inPar INT = 345;
DECLARE @outPar INT;

Let us create and see the table:

Query:

Create table TestOrders (OId int, PersonId int) ;
insert into TestOrders values(2, 345), (3, 657), (4, 789) ;
SELECT TOP 1000 [OId]   ,[PersonId] FROM [TestOrders];

Output:

Input Parameter:

The caller can send a data value to the stored procedure or function via input parameters. The stored method can return a data value or a cursor variable to the caller using output parameters. The caller receives an integer return code from every stored procedure.

Example of Input Parameter

— inPar is the  Input Parameter

DECLARE @inPar INT = 345;
DECLARE @test NVARCHAR(MAX) = N'SELECT TOP 1 [OId]
FROM TestOrders WHERE PersonId = @id_in';
EXEC sys.sp_executesql @test, N'@id_in INT', @inPar;
GO

Output:

Output Parameter:

A parameter whose value is sent out of the stored procedure/function module and back to the calling PL/SQL block is known as an output parameter. A variable, not a constant, must be used as an OUT parameter.

Example:

— outPar is the Output Parameter

DECLARE @outPar INT;
DECLARE @test NVARCHAR(MAX) = N'SELECT TOP 1 @id_out = [OId]
FROM TestOrders WHERE PersonId = 345';
EXEC sys.sp_executesql @test, N'@id_out INT OUT', @outPar OUT;
SELECT @outPar OutParValue
GO

Output:

Example of Input/Output Parameter:

DECLARE @inPar INT = 345;
DECLARE @outPar INT;
DECLARE @test NVARCHAR(MAX) = N'SELECT TOP 1 @id_out = [OId]
FROM [TestOrders] WHERE [PersonId] = @id_in';
EXEC sys.sp_executesql @test, N'@id_in INT, @id_out INT OUT',
@inPar, @outPar OUT;
SELECT @outPar OutParValue
GO

Output: