lb4 ms-sql : Stored procedures with input and output parameters

14 views
Skip to first unread message

Pieter Coetzer

unread,
Feb 25, 2021, 4:51:10 PM2/25/21
to LoopbackJS
Hi all,

I'm trying to execute a ms-sql stored procedure that has some input parameters but also has output parameters.

In SQL Management Studio I would run the query like:

DECLARE 
          @TOTALRECORDS as INT,
          @TOTALPAGES as INT

          exec [dbo].[history_data_paged]
              '12345676543',                              --  ID
              '2021-01-01',                                   -- Start Date
              '2021-01-30',                                   -- End Date
              1,                                                       -- Page
              20,                                                     -- Records per page
              @TOTALRECORDS,                        -- total records counter
              @TOTALPAGES                              -- total pages (depending on the Records Per Page)


I'm using a repository class that handles queries to the datasource using the following function: 

async query(sql: string, params?: any, options?: any): Promise<any> {
    return new Promise((resolve, reject) => {
      const connector = this.dataSource.connector!;
      connector.execute!(sql, params, options, (err: any, ...results: any) => {
        if (err) {
          return reject(err);
        }

        if (results.length === 0) {
          return resolve([]);
        }

        if (results.length === 1) {
          return resolve(results[0]);
        }

        resolve(results);
      });
    });
  }


How would I then set up the query command to cater for the output parameters?? 


Thanks in advance.





Reply all
Reply to author
Forward
0 new messages