T-sql Web servcies

You should be able to do it similarly to this:

CREATE PROCEDURE GetAirportInfo
@AirportCode varchar(20)
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.net/airport.asmx/getAirportInformationByAirportCode?airportCode=' + @AirportCode,'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object
END

Select allOpen in new window

Which SQL Server are you using SQL 2008  ?

For that i think you need to use SQL SERVER CLR or SSIS package.
For clr refer - https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/
For SSIS package - it is same as you call it in .NET code. here you need to use the script task.

Otherwise you can check the below code. TO consume a webservice you need 3 sps called sp_OACreate, sp_OAMethod and sp_OADestroy. I have created a stored procedure for you. If you want to pass the webservice name dynamically you can change it to parameters.

CREATE PROC CallWebService
AS
BEGIN

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', 'http://ws.afd.co.uk/PCE_WebService.asmx?WSDL','false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob

END

Select allOpen in new window

If you want to learn the entire code then you can follow from codeproject
https://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur

Hi, I tried above codes many times.Never worked.That's the reason i asking how to do that.
if you go to
http://ws.afd.co.uk/PCE_WebService.asmx?WSDL

i'm looking how to pass parameters.

PCEWebService.PostcodeEverywhere.AddressList(String strSerialNumber, String strPassword, String strUserID, String strPostcode, String strProperty, String strCountyType)

Hi  Tried using following way but it's working in the IE explorer
but not in the SQL

http://ws.afd.co.uk/PCE_WebService.asmx/AddressList?strSerialNumber=1111&strPassword=xxxxx&strUserID=sert&strPostcode=NE3%202NS&strProperty=&strCountyType=

>> I tried above codes many times.Never worked.That's the reason i asking how to do that.

asking again - Which version of SQL Server are you using?

@Kyle Abrahams
I used same exactly like you used,But still not working see my code

http://ws.afd.co.uk/PCE_WebService.asmx/AddressList?strSerialNumber=1111&strPassword=444&strUserID=Test1&strPostcode=NE3%202NS&strProperty=&strCountyType=Tyne

This is working in the IE explore,But not the SQL server

and also i'm getting error message From  your code  +   (+ @AirportCode) Incorrect syntax near +

 Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.net/airport.asmx/getAirportInformationByAirportCode?airportCode=' + @AirportCode,'false'

@Pawan Kumar
Sql Server 2008R2

can you pls share your entire code. Will fix.

@Pawan Kumar:
Still NULL retun
But sameway it's working at IE browser

Al@author

Do u know any parameter values where for which it will return value?

Select @ReturnData?

I think is once you pass proper values of parameters then you will get proper response if it is there. My code is working fine.

EXEC CallWebService '1111','444','Test1','NE3 2NS','','Tyne'

Select allOpen in new window

Question abandoned
Provided multiple solutions.