How does one call a SQL Server scalar function using the mssql package for nodejs?

Question

I have a custom scalar function in SQL Server 2012. It takes 2 floats and returns a nvarchar. Tested and works inside SQL Server.

I've not found any examples of how to call it from node-mssql. The attempts I've made fail with:

TypeError: Cannot set property 'value' of undefined

Which appears to be an error from the node-tedious package...so possibly tedious doesn't support it yet.

I have a working version that calls a Stored Procedure with two input parameters that returns a recordset, but as I really only need one value, it seems like it's over kill, and an executeScalar call would be useful here (something I'm used to having from .NET).

Answer 1

You can skip the input/output setup in node-mssql and simply select the scalar function just as you would normally.

Function

CREATE FUNCTION [dbo].[udfTestScalar] (
    -- Add the parameters for the function here
    @F1 FLOAT
    ,@F2 FLOAT
    )
RETURNS NVARCHAR(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @N1 NVARCHAR(100)

    -- Add the T-SQL statements to compute the return value here
    SET @N1 = N'Hi there'

    RETURN @N1
END

index.js

var sql = require('mssql');

var config = {
    user: 'user',
    password: 'password',
    server: 'localhost',
    database: 'db'
};

var connection = new sql.Connection(config, function(err) {
    var request = new sql.Request(connection);
    request.query("SELECT dbo.udfTestScalar(12345.123, 12345.456) as result", function(err, recordsets) {
        console.log(recordsets[0].result);
    });
});

Output

% node index.js
'Hi there'