Stored Procedures

[scottchantry]

I don't know if you already have support for calling stored procedures. If you do, how do you call them? If not, would it take much effort to add support for stored procedures? Thanks!

[pekim]

It should be possible to call stored procedures now, but in a quite limited way. If you don't need any output parameters, then you can use a Request to execute a statement that just happens to be a stored procedure.

For example, sp_who accepts no parameters, and outputs a result set. So it can be treated the same as the execution of any select statement.

Input parameters can passed using string concatention.

var sql = "exec my_proc @myParam = '" + value + "'";
new Request(sql, ...)

Of course this is horrible, and not a good strategy. Issue #14 will introduce support for execution of parameterised requests, which will remove the need for that approach. I intend to work on it soon, and expect it to be in the next release. It will only support a small number of data types for parameters intially.

Once parameterised requests are in place, it shouldn't require too much work to provide a decent API for execution of stored procedures.

[jschuhr]

I don't mind running "exec my_proc" strings as SQL statements; it seems to work except the row count comes back undefined in these cases.

[pekim]

Support for executing stored procedures, including passing input and output parameters has been added.
So far only tinyint, smallint, int, varchar and nvarchar are supported.

The documentation has not been updated yet.

[ReiGarcia]

Could you post a sample code to call a stored procedure using input/output parameters?
Thanks.

[pekim]

var TYPES = require('tedious').TYPES;

var request = new Request('my_proc', function(err) {
    ....
});

var string = 'some text';
var number = 7;

request.addParameter(TYPES.VarChar, 'parameterName', string);
request.addParameter(TYPES.Int, 'anotherParameterName', number);
request.addOutputParameter(TYPES.VarChar, 'outputParameterName', {length: 10});    // varchar(10)

request.on('returnValue', function(parameterName, value, metadata) {
    console.log(parameterName + ' = ' + value);      // outputParameterName = ...
});

connection.callProcedure(request);

Note: I am not convinced that the API is quite right. There's a good chance that it will change.

[ReiGarcia]

Thanks!

[pekim]

Please note, the first two arguments to the Request.addParameter and Request.addOutputParameter functions have been swapped around. The first argument is now the parameter name.

request.addParameter('parameterName', TYPES.VarChar, string);
request.addParameter('anotherParameterName', TYPES.Int, number);

[pekim]

Included in v0.0.4.