Geek Answers Handbook

Web Api 2 endpoint call from stored procedure

some legacy SQL stored procedure (SQL Server 2008) should get some information from the web service (Web Api 2) that I provided.

How to call the endpoint (in GET), get the data (JSON format) and translate the data received into the temporary table that will be used in the rest of sproc?

thank

+4
source share
3 answers

The best way would be to create a CLR function defined using the function and call your web API from there so that you can use all the features of the C # and .Net libraries to make web calls and Json analysis. There is enough information on the Internet about this. For example: https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/ . It's not about WebAPI in particular, but you can get the idea from there.

Note that this requires deploying a custom assembly with CLR functions to SQL Server.

Edit :

TSQL OLE-. , , , , , , CLR.

+5

CLR:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object
+4

Try using this CLR stored procedure: SQL-APIConsumer

 exec  [dbo].[APICaller_POST]
       @URL = 'http://localhost:5000/api/auth/login'
      ,@BodyJson = '{"Username":"gdiaz","Password":"password"}'
0
source

More articles:


All Articles