I'm part of a team working on an Internet site that uses lots of SQL Server tables to drive HTML Select lists. On many pages, for example, users can enter a country or state by simply selecting the entry from a list. We used Active Server Pages (ASP) to generate the lists, as most applications do, and eventually created functions to generate the list and stored those functions in an Include file. However, ASP requires extra time on the server to load and process the Include file. In addition, you must use the entire Include file every time you want to create a Select list from a table.
On the way to a client site recently, I had an idea. What if we put the functions in a stored procedure instead of an Include file and let the stored procedure build the option list for the Select statement? I began experimenting. The following code, version 1 of this approach, generates a record set formatted correctly for an option list. (I'll explore a more-detailed version in the next couple of weeks as our testing evolves.)
First, I use T-SQL's concatenation feature to create a Select statement that builds the option list from the generated the record set:
Alter Procedure "getOptionListStates"
As
select ('')
as OptionList from states
Executing the getOptionListStates stored procedure results in this record set:
You could also generate the option list directly in the database.
The next step is to modify the stored procedure to return a single variable that contains the complete option list. You then simply feed that return variable back to the HTML Select statement.
Our team hasn't compared the stored procedure approach's performance with the ASP approach's. That step is next. What I find most interesting about this technique, though, is being able to use SQL Server to automatically generate part of the HTML we normally put in our ASP code. This mixed approach to building ASP code should result in a solution that performs better and is more flexible than pure ASP. For instance, if you create a flexible stored procedure that builds a Select option list, you can easily use that stored procedure from any number of applications, even applications on different servers. That's hard to do when you bury all of your code in an Include file.
