Tuesday, January 5, 2016

Invoke HTTP Web service from T-SQL in Sql Server

I was asked this question by a friend of mine sometime last year. I could find answer to the question via a quick Google search, but was not able to figure out when one will need to do invoke HTTP web service from a stored procedure. While talking to a colleague about work in next cycle, figured out when this might be useful.

Consider the following case -

We have the following components -

1) C# Code - Web service
2) SQL Server Database
3) Java Web Service which accepts byte array as an input parameter and returns object which is saved in SQL Server Database.
4) Applications which consume the web service in #1 above.

The web service fetches values from database and invokes Java web service. So this operation can be replaced by the following as follows -

1)A stored procedure invoking the Java web service.
2) Applications can call the stored procedure. This approach should be faster.

Note -
1) Web services should be the way to go, direct stored procedures calls should be avoided as far as possible, but wanted to think situations when calling HTTP web services from stored procedures makes sense.

What do you think? 


Sharath said...

Why cant you have the Apps calling Java Web Service and updating the DB.

Priyaranjan Anand Marathe said...

That will mean same code replicated at different places.

Sharath said...

You can do away with C# WebService.

Priyaranjan Anand Marathe said...

Yeah. And use the Java web service from each of the apps?