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);