Can you call a webservice from TSQL code?

Not in T-SQL code itself, but with SQL Server 2005 and above, they've enabled the ability to write CLR stored procedures, which are essentially functions in .NET code and then expose them as stored procedures for consumption. You have most of the .NET framework at your fingertips for this, so I can see consumption of a web service possible through this.

It is a little lengthy to discuss in detail here, but here's a link to an MSDN article on the topic.

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

Sure you can, but this is a terrible idea.

As web-service calls may take arbitrary amounts of time, and randomly fail, depending on how many games of counterstrike are being played on your network at the time, you can't tell how long this is going to take.

At the bare minimum you're looking at probably half a second by the time it builds the XML, sends the HTTP request to the remote server, which then has to parse the XML and send a response back.

1. Whichever application did the INSERT INTO BLAH query which caused the web-service to fire is going to have to wait for it to finish. Unless this is something that only happens in the background like a daily scheduled task, your app's performance is going to bomb

2. The web service-invoking code runs inside SQL server, and uses up it's resources. As it's going to take a long time to wait for the HTTP request, you'll end up using up a lot of resources, which will again hurt the performance of your server.

I would not do this for heavy traffic or mission critical stuff, HOWEVER, if you do NOT need to receive feedback from a service, then it is actually a great thing to do.

Here is an example of what I have done.

1. Triggers Insert and Update on a Table
2. Trigger called Stored Proc that is passes the JSON data of the transaction to a Web Api Endpoint that then Inserts into a MongoDB in AWS.

Don't do old XML

JSON

EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST', 'http://server/api/method', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len INT = len(@requestBody) 

Full example:

Alter Procedure yoursprocname

 @WavName varchar(50),
 @Dnis char(4) 

    AS
BEGIN

    SET NOCOUNT ON;


DECLARE @Object INT;
DECLARE @Status INT;


DECLARE @requestBody NVARCHAR(MAX) = '{
"WavName": "{WavName}",
"Dnis": "{Dnis}"
}'


SET @requestBody = REPLACE(@requestBody, '{WavName}', @WavName)
SET @requestBody = REPLACE(@requestBody, '{Dnis}', @Dnis)


EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST',  'http://server/api/method', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len INT = len(@requestBody) 
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'send', null, @requestBody
EXEC sp_OAGetProperty @Object, 'Status', @Status OUT
EXEC sp_OADestroy @Object