Using Parameters

There is support for parameterised statements, and using parameters when calling procedures.

Input parameters are added to a Request using Request.addParameter(), and output parameters are added using Request.addOutputParameter()

Parameter names

In T-SQL, parameter names are identified by being prefixed with an '@'.

 select id from address where first_name = @name and age > @age

The parameters names used in this API do not include the '@'.

 request.addParameter('name', ..., ...);
 request.addParameter('age', ..., ...);

Input Parameters

This is how input parameters might be used when executing a statement.

 var TYPES = require('tedious').TYPES;
 var sql = 'select id from address where first_name = @name and age > @age';
 var request = new Request(sql, function(err) {
 ....
 });
 request.addParameter('name', TYPES.VarChar, 'Fred');
 request.addParameter('age', TYPES.Int, 42);
 connection.execSql(request);

Calling a stored procedure with parameters works in a similar way.

 var TYPES = require('tedious').TYPES;
 var request = new Request('get_addresses', function(err) {
 ....
 });
 request.addParameter('name', TYPES.VarChar, 'Fred');
 request.addParameter('age', TYPES.Int, 42);
 connection.callProcedure(request);

Output Parameters

The values for output parameters are provided with emitted returnValue events.

 var TYPES = require('tedious').TYPES;
 var request = new Request("select @number=42, @string='qaz'", function(err) {
 ....
 });
 request.addOutputParameter('number', TYPES.Int);
 request.addOutputParameter('string', TYPES.VarChar);
 request.on('returnValue', function(parameterName, value, metadata) {
 console.log(parameterName + ' = ' + value); // number = 42
 // string = qaz
 });
 connection.execSql(request);

Table-Valued Parameters

TVP are only supported when using callProcedure.

 var TYPES = require('tedious').TYPES;
 
 var table = {
 columns: [
 {name: 'user_id', type: TYPES.Int},
 {name: 'user_name', type: TYPES.VarChar, length: 500},
 {name: 'user_enabled', type: TYPES.Bit}
 ],
 rows: [
 [15, 'Eric', true],
 [16, 'John', false]
 ]
 };
 var request = new Request("my_stored_procedure_name", function(err) {
 ....
 });
 request.addParameter('tvp', TYPES.TVP, table);
 connection.callProcedure(request);