Running a Stored Procedure with Nodejs and Mssql Package Error

Running a stored procedure with NodeJS and MSSQL package error

I think the line's

req.input('ProductEntryID', req.Int, 3299);
req.input('LoginEntryID', req.Int, 4);
req.input('TempLoginEntryId', req.Int, -1);
req.input('AddToWishList', req.Bit, 0);
req.input('WebPortalId', req.Int, 0);

which has req.input thats wrong it seems.

Please try this code

var sql = require('mssql');

var config = {
    user: 'sa',
    password: '---',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: 'Test'
}

var getCities = function() {
  var conn = new sql.Connection(config);
  conn.connect().then(function(conn) {
    var request = new sql.Request(conn);
    request.input('City', sql.VarChar(30), 'Cbe');
    request.input('NameNew', sql.VarChar(30), 'Cbe');
    request.execute('spTest').then(function(err, recordsets, returnValue, affected) {
      console.dir(recordsets);
      console.dir(err);
    }).catch(function(err) {
      console.log(err);
    });
  });
}

getCities();

I tried this myself and its giving the results.

node-mssql won't execute stored procedure giving unhandlePromiseRejectionWarning

I found the issue. It was my original code.

            .input('FBID', sql2.varchar, 250, req.query.id)
            .input('FBEmail', sql2.varchar, 250, req.query.email)
            .input('FBName', sql2.varchar, 250, req.query.memberName)
            .input('FamilyName', sql2.varchar, 250, req.query.familyName)

Needed to be changed to:

            .input('FBID', sql2.VarChar(250), req.query.id)
            .input('FBEmail', sql2.VarChar(250), req.query.email)
            .input('FBName', sql2.VarChar(250), req.query.memberName)
            .input('FamilyName', sql2.VarChar(250), req.query.familyName)

Ultimately I had the size as part of the value parameter. It was all wrong. Once I did it correctly, then the stored procedure executed as expected.

Node.js and MSSQL stored procedures

The linked document does actually mention stored procedures:

var request = new sql.Request(connection);
    request.input('input_parameter', sql.Int, 10);
    request.output('output_parameter', sql.VarChar(50));
    request.execute('procedure_name', function(err, recordsets, returnValue) {
        // ... error checks

        console.dir(recordsets);
    });

Not sure it's wise to answer this question, but it might be valueable for future readers/googlers.

Stored Procudure SQL error in Node `mssql` package

The correct syntax is:

UPDATE c
    SET UPRBCHOR = u.uprbchor,
        BACHNUMB = u.bachnumb,
        TRU_TYPE_ID = 132
FROM dbo.C2980251 c LEFT JOIN
     CFRTR.dbo.UPR10301 u
     ON u.BACHNUMB = c.BACHNUMB
WHERE u.BACHNUMB Is Null

Notes:

• SQL Server requires a separate FROM clause.
• Table aliases make the query much easier to write and read.
• A LEFT JOIN makes much more sense here than a RIGHT JOIN. The database cannot update records that don't exist.
• u.uprbchor and u.bachnumb are going to be NULL because the WHERE clause only keeps rows with no matches.

How to execute stored procedure as well as select command using nodejs-mssql

var sql = require('mssql');

sql.connect("mssql://username:password@localhost/database", function(err) {
    // ... error checks

    new sql.Request()
    .input('name', 'new folder')
    .input('FilterTypeID', 1)
    .execute('sp_isFolderExist', function(err, recordsets, returnValue) {
        // ... error checks

        console.log(returnValue); // your isTrue value
    });
});

Executing a stored procedure in Node.js express application making use of mssql package provided by Node.js

There are many ways of doing this and many packages that can help. I would recommend the Knex.js package. Once you've set that up and made a connection, you can then use the knex.raw function to execute arbitrary SQL and have it returned as a knex object. I'm not sure of the specific SQL syntax for MSSQL, but it should be very similar to Postgres where you would do something like:

knex.raw('select * from my_func(?, ?)', [valOne, valTwo]);

In the above example I am running a select query against a stored procedure called my_func. I am them passing in a question mark for each parameter, and then matching those up in an array after the string. This will result in the SQL being executed.

select * from my_funct(valOne, valTwo);

This includes escaping values to help defend against things such as SQL injection.

Your execution syntax may be slightly different in MSSQL, but you can still use knex.raw and the question mark + array syntax to inject values into a prepared statement like this.

---

Related Topics