Exposing Stored Procedures from MSSQL using loopback 4

[neha2683]

Hello

Able to access the DB tables from MS SQL using loopback 4 .

Looking for options to expose only the Stored Procedures from the table , and expose them as Rest APIs . Kindly help. Thanks.

[dhmlau]

Cross posting from https://github.com/strongloop/loopback-next/issues/3798#issuecomment-535119705.

You can call DataSource.execute(), something like below:

  @get('/test', {
    responses: {
      '200': {
        description: 'test for executing query',
      },
    },
  })
  async test(): Promise<any> {
    return this.customerRepository.dataSource.execute('SELECT * FROM CUSTOMER');
  }

[neha2683]

Thanks Diana . But what I want is to call 2000+ stored procedures via REST API through loopback. Is there a way to do that ? Thanks in advance .

[dhmlau]

@neha2683, do you mean you want one REST API that calls 2000+ stored procedures, or 2000+ endpoints that each call one stored procedure?

[neha2683]

The latter one.... Procedure name and input parameters as inputs to one Rest Api.

On Tue, Apr 14, 2020, 9:52 PM Diana Lau notifications@github.com wrote:

@neha2683 https://github.com/neha2683, do you mean you want one REST API that calls 2000+ stored procedures, or 2000+ endpoints that each call one stored procedure?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/strongloop/loopback-next/issues/5101#issuecomment-613540849, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHGF7S7ADQCCYJ3NZWTQYALRMSESPANCNFSM4MG4RYRQ .

[neha2683]

hello i need to create APIs like this ( as available in dreamfactory ) . How to do with loopback.

GET /_proc/{procedure_name} Call a stored procedure.

POST /_proc/{procedure_name} Call a stored procedure.

[dhmlau]

@neha2683, it would be something similar to what I posted in https://github.com/strongloop/loopback-next/issues/5101#issuecomment-613189957. Controllers are used to expose REST APIs, so add those functions in the controller.

[deepakrkris]

@neha2683 please take a look at https://github.com/strongloop/loopback-next/issues/3459

constructor(
    @repository(MyRepository)
    public myRepo: MyRepository,
  ) {
    this.user_Profile = userProfile;
  }
 async testData(
  ): Promise<any> {
    return await this.myRepo.dataSource.execute(`EXEC Get_Customer(?)`, ['CUSTID01']);
  }

and

https://loopback.io/doc/en/lb4/apidocs.repository.connector.execute.html

[dhmlau]

Closing as resolved.