SQL parameters are defined in the recordset editor's parameter list and the Parameter window.

Input and output parameters in the database engine

In the database engine, an Input parameter is declared as variable, and set to a value. The variable is accessible in the SQL script.

SELECT * FROM Customers WHERE CustomerID = @CustomerID -- T-SQL example

An output parameter is also declared as a variable in the database engine before executing the SQL script, but it is not initialized with a value. Instead the value is assigned in the SQL script. The value is then returned by the database engine to the recordset.

SET @TotalAmount = 350.00 -- T-SQL example

When a parameter is set to both input and output, the variable will be initialized with a value before the SQL script is executed, and when the SQL script completes, the current value is returned to the recordset.

Input parameters for executing the SQL script for a single recordset

The parameters are set with Recordset.ExecSql()

Recordset.ExecSql(connector, [parameter] [,parameter2] [,parameter3])

var customers = new PriKey_Customers_Recordset();

customers.MaxRows = 100;

customers.ExecSql(25200); // 25200 is the customer ID

Input parameters for executing the SQL script for multiple recordsets in a single database transaction

The parameters are set with Recordset.SetExecSqlParams() method or the Recordset.InputParam property.

Recordset.SetExecSqlParams( parameter [,parameter2] [,parameter3])

Transactional.ExecSql(Recordset [,Recordset2] [,Recordset3])



invoices.SetExecSqlParams(1, 10022);

Transactional.ExecSql(customers, orders, invoices);

Setting input parameters through the Recordset.InputParam property

This is equivalent to Recordset.SetExecSqlParams(), only here the input parameters are set as properties.

Recordset.InputParam.Property = value;

Recordset.InputParam.Property2 = value;

Recordset.InputParam.Property3 = value;

Transactional.ExecSql(Recordset [,Recordset2] [,Recordset3])

customers.InputParam.CustomerNumber = 100;

orderlines.InputParam.OrderNumber = 12005;

orderlines.InputParam.OrderLineNumber = 1;

Transactional.ExecSql(customers, orderlines);

Recordsets will remember their input parameter values after Transactional.ExecSql completes running.

Output parameters

Output parameter values are retrieved through the Recordset.OutputParam properties.


var sum = total_order_amount.OutputParam.TotalAmount;

MessageBox.Show($"The total amount is {sum}");