HTTP Endpoints in SQL Server 2005
Among the many new features in SQL Server 2005 is the ability to create HTTP endpoints by running T-SQL code. HTTP endpoints have several uses, including setting up SQL Service Broker connections and database mirroring over TCP/IP, but the one we'll be concerned with here is that HTTP endpoints make it easy to build Web services that return SQL Server data. In fact, if your copy of SQL Server 2005 is running on Windows Server 2003, you don't even need to have IIS installed to create a Web service that returns SQL Server data. A stored procedure coupled with a CREATE ENDPOINT statement will do the trick.
As with many of the other advanced parts of T-SQL, though, the CREATE ENDPOINT statement has a good many optional clauses and a lot of complexity. If you're going to need it more than once or twice, that offers an ideal opening for code generation. Rather than deal with that complexity all the time, figure it out once and embed your knowledge in a CodeSmith template. To begin with, you'll need an example of the SQL that you want to create.