Exposing Stored Procedures as Web Services

Posted Monday, March 7, 2005 9:12 AM by C-Dog's .NET Tip of the Day
As you may have heard, SQL Server 2005 can exposed just about anything now via Web Services.  This will often eliminate the need of creating a middle tier object to perform data access for you.  The way it works is by creating an HTTP EndPoint.  I would like to think that they will have something integrated into the IDE to take care of this for you, but so far everything I have seen has required a few T-SQL statements.
Here is an example of how an endpoint is created for a stored procedure GetContent.
     PATH = '/Local',
     PORTS = (CLEAR),
     SITE = 'tweb24'
     WEBMETHOD 'GetContent'
         (name = 'teamowner.dbo.GetContent',
          schema = STANDARD)
     DATABASE = 'teamowner',
     NAMESPACE = 'http://www.thrifty.com/schemas/local'
There are quite a bit of parameters here but most of it is straight forwards.  This example creates an endpoint at tweb24/local/ContentWebService (notice there is no asmx extension).  You can get the WSDL by passing ?WSDL to the previous URL.  At that point you can reference the web service in a project like any other.
Here is what some of the parameters do.  PATH sets the path that the web service is going to be located at.  The name of the endpoint will be appended to the path.  The PORTS parameter sets which port it will be exposed on (a value of CLEAR indicates port 80).  The WEBMETHOD actually specifies the name of the web method and which stored procedure it will execute (in this case GetContent).   There are a ton of other parameters that can be used with this and I am not sure what all of the do yet.  I imagine I will post more on this in the future.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=162