The Desired SQL Statements

Owned by Tom DuPont (Unlicensed)
Last updated: Jan 17, 2012 by Autumn Wilkins (Unlicensed)
2 min read

We're going to create an HTTP endpoint that returns all of the data from a particular table. Because HTTP endpoints can only return information from stored procedures or functions, this means we'll actually have to build two SQL statements: one to create a stored procedure, and one to create the endpoint itself. As usual, the easiest way to build a CodeSmith template is to start with a copy of the output that you want to produce. In this case, here are the SQL statements to build an HTTP endpoint based on the Person.AddressType table in the AdventureWorks sample database:

CREATE PROC dbo.PersonAddressTypeProc
AS
    SELECT 
         AddressTypeID, 
         Name, 
         rowguid, 
         ModifiedDate
     FROM
         Person.AddressType
GO
>CREATE ENDPOINT GetAddressType
     STATE = STARTED
AS HTTP
(
     PATH = '/AddressType',
     AUTHENTICATION = (INTEGRATED),
     PORTS = (CLEAR),
     SITE = 'localhost'
)
FOR SOAP
(
     WEBMETHOD 'AddressTypeList'
         (NAME='AdventureWorks.dbo.PersonAddressTypeProc'),
     BATCHES = DISABLED,
     WSDL = DEFAULT,
     DATABASE = 'AdventureWorks',
     NAMESPACE = 'http://AdventureWorks/AddressType'
)
GO 

Now that we understand where we're headed, we can start the journey. This time, we'll use CodeSmith Generator as our tool, to get a sense of the support that it offers for quickly writing templates.We've highlighted two types of information in the statements above. The red highlights show parts of the SQL statements that the user can choose from a small list of possibilities. The green highlights show information that CodeSmith Generator can determine from the SQL Server database after the user specifies a database table. The rest of the template will just be static text.

Next: Creating the Template in CodeSmith Generator

Looking at the SQL

You don't really need to understand the ins and outs of the CREATE ENDPOINT statement to follow along with this tutorial, but you might like to know what's going on here anyhow. Here are a few notes on the various clauses in this SQL statement:

  • The STATE clause specifies the initial state of the endpoint. It can be started, stopped (listening but returning errors to clients) or disabled (not evening listening for requests)
  • The AS HTTP clause specifies the transport protocol to use. You can also specify AS TCP here.
  • The PATH clause specifies the URL on the server that clients will use to reach this Web service.
  • The AUTHENTICATION clause specifies how clients will authenticate themselves to the SQL Server: BASIC, DIGEST, NTLM, KERBEROS, or INTEGRATED.
  • The PORTS clause specifies whether the service will listen on the CLEAR or SSL ports, or both (other clauses, not shown here, let you specify non-standard port numbers)
  • The SITE clause lets you specify a hostname for the computer that will respond to requests.
  • The FOR SOAP clause states that this endpoint will respond to SOAP messages.
  • The WEBMETHOD clause defines a Web method, mapping a method name to the name of a stored procedure
  • The BATCHES clause specifies that this endpoint won't process arbitrary SQL statements.
  • The WSDL clause specifies that it will provide WSDL support.
  • The DATABASE clause specifies the database that contains the data.
  • The NAMESPACE clause specifies the XML namespace for the messages.