Menu
Is there a way to call out from a TSQL stored procedure or function to a webservice?
496
In earlier versions of Sql, you could use either an extended stored proc or xp_cmdshell to shell out and call a webservice. Not that either of these sound like a decent architecture - but sometimes you have to do crazy stuff. Show activity on this post. You can do it with the embedded VB objects.
1) GET methodCreate an OLE object using the sp_OACreate procedure. Pass the created OLE object and make an HTTP request call. Handle the response received from API. Parse the JSON records and insert/ update in the desired table.
Autonomous REST Connector allows you to query API data using standard SQL by normalizing the semi-structured JSON response data into structured data that can be used with SQL. There's no need to understand the complexities of the JSON response to start effectively working with the data.
Yes , you can create like this
CREATE PROCEDURE CALLWEBSERVICE(@Para1 ,@Para2) AS BEGIN Declare @Object as Int; Declare @ResponseText as Varchar(8000); Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT','false' Exec sp_OAMethod @Object, 'send' Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT Select @ResponseText Exec sp_OADestroy @Object END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
