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 #3798 (comment).

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 ***@***.***> 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 <#5101 (comment)>, 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 #5101 (comment). Controllers are used to expose REST APIs, so add those functions in the controller.

[deepakrkris]

@neha2683 please take a look at #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.