0

I am using loopback3 api for an app to create new users. My remote-method '/newUser' execs a stored procedure, but is failing to create the new user.

App Flow: create new user by running 3 stored procedures: usp_CreateUser, which returns the UserID via @UserIdOutputParam. usp_CreatePhone and usp_CreateEmail are both executed from within usp_CreateUser, and use the @UserIdOutputParam as the FK to create the linked phone and email.

The problem: running 'newUser' remote method in loopback, which executes the usp_CreateUser, the @UserIdOutputParam is not able to get passed back into the usp_CreateUser, and the code errors out there/new user is not created.

Tried hardcoding the UserIdOutputParam to be NULL, tried omitting it.

I reviewed node-mssql & tediousjs parameters examples, but don't see how to use them with my loopback remote-method.

node-mssql:

const request = new sql.Request()
request.input('input_parameter', sql.Int, value)
request.output('output_parameter', sql.Int)
request.execute('procedure_name', (err, result) => {

tedious:

request.addOutputParameter('number', TYPES.Int);
  request.addOutputParameter('string', TYPES.VarChar);

Here's my code

'use strict';
module.exports = function (Person) {
  var server = require('../../server/server');
  var ds = server.dataSources.MSSQL_DB;
  Person.newUser = async function (
    firstname,
    lastname,
    phonenumber,
    emailaddress,
    //    useridoutputparam,
    request
  ) {
    request = ("EXEC dbo.usp_CreateUser" +
      "@FirstName='" + firstname + "', "
      + "@LastName='" + lastname + 
      + "@PhoneNumber=" + phonenumber + ", "
      + "@EmailAddress=" + emailaddress + ", "
      + "@UserIdOutputParam=" + null

      + ";");
    ds.connector.execute(request, function (err, data) {
      console.log(request);
      if (err) {
        console.log(err);
      } else {
        console.info("data", data);
        return (err, data);
        ;
      };
    }
    );
  };
  Person.remoteMethod('newUser', {
    accepts: [
      { arg: 'firstname', type: 'string', required: false },
      { arg: 'lastname', type: 'string', required: false },
      { arg: 'phonenumber', type: 'number', required: false },
      { arg: 'emailaddress', type: 'string', required: false },
      { arg: 'useridoutputparam', type: 'string', required: false }
    ],
    http: { path: '/newUser', verb: 'post' },
    returns: { arg: 'data', type: 'string' };
  }
  );
  //more info: https://loopback.io/doc/en/lb3/Remote-methods.html
};

I expect the remote method to exec the stored procedure, but it fails without the output parameter.

Error from console:

EXEC dbo.usp_CreateUser @FirstName='Buzz', @LastName='Lightyear', @EmailAddress='blightyear@allenmatkins.com', @PhoneNumber=7072738888, { RequestError: Procedure or function 'usp_CreateUser' expects parameter '@UserIdOutputParam', which was not supplied.

Manny Lornu
  • 1
  • 1
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Jun 29 '19 at 04:08
  • Thanks for the editing. No responses to this question. Hmm, anyone know how to do this in Loopback 4?? – Manny Lornu Jul 02 '19 at 21:31

0 Answers0