How to Call a Web Service in SQL Server?

We can create a function in Sql Server to call .net dll which can contain web service call or any .net code.

CREATE FUNCTION <[Function Name]>(<parameter(s)>)
RETURNS <Return Type> WITH EXECUTE AS CALLER
AS
EXTERNAL NAME <Assembly Name>.<Class Name>.<Method Name>


Example:

CREATE FUNCTION GetDataFromXYZService(@ID [navarchar](10))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [AssemblyToGetDataFromXYZService].[GetDataClass].[GetDataMethod]

here AssemblyToGetDataFromXYZService is SQL assembly.

click here to check how to create AssemblyToGetDataFromXYZService assembly in SQL server using .net assembly.