Using stored procedures skillfully in ASP

On many pages, users can enter the name of a country or state by simply selecting one of the items in the list, and in most cases, we typically use ASP to build the list, create the functions that generate the list, and save it in an include file. However, ASPs need additional time to load and process these include files on the server, and the server must load the entire include file each time a select list is generated.

When I recently built a Web site for a customer, I had a new idea to put these functions in a stored procedure instead of an include file, and let the stored procedure generate a select list. The following code generates a record of the select list.

First, use the T-SQL concatenation feature to create a SELECT statement that generates a selection manifest from a recordset:

Alter Procedure "Getoptionliststates"

As

Select ('

+ RTrim (FullName) + ")

As Optionlist from states

The following record can be generated by executing the getoptionliststates stored procedure:

North Carolina

South Carolina

Washington

Virginia

Of course, you can also directly put the selection list in the database.

You can then modify the stored procedure to return a single variable that contains all the selection list elements, and then return the variable to the HTML SELECT statement.

While there is no detailed comparison of the performance differences between the use of stored procedures and ASP, I find that the most interesting aspect of using a stored procedure is that you can use SQL Server to automatically generate part of the HTML code that we need to complete in ASP before. Using this hybrid method to generate ASP code is superior to pure ASP code in terms of performance and flexibility. For example, once a stored procedure is established, it can be easily invoked in different applications, even in applications on different servers, and it is difficult to put all the code in the include file.
Turn from: Dynamic Network production guide www.knowsky.com