SQL Server Magazine

Here's how you can build a simple but powerful Web search tool for your data warehouse or database. Unlike many Web front ends, this project, which WebSQL subscribers can download at the link to this article at http://www.sqlmag.com, puts most of the search logic in the database. For this project, you use basic HTML input objects to collect the search criteria. After your Web server receives the parameters, they are included in an EXEC statement that calls a stored procedure. After executing, this stored procedure returns a recordset, which the server formats into HTML and sends to the browser.

The power in this approach resides in your use of the stored procedure. A server-based procedure provides precompiled access to all the data warehouse tables, thereby enhancing performance. And because one of the procedure parameters is the search type, one Web page calling one procedure can return a nearly limitless number of search types, plus column headings or the number of columns displayed.

In this article, I also show you how to format the text so that users can easily copy their results into any text editor. And I show you how the stored procedure can embed a hyperlink into the recordset sent back to the browser.

This project uses a three-tiered browser, Web server, database architecture. You need SQL Server 6.x or 7.0, Windows NT 4.0 with Internet Information Server (IIS) 3.0 or 4.0, and Active Server Pages (ASP) with ADO version 1.0, 1.5, or 2.x. And you need to be familiar with basic HTML commands and know how to build ASP pages and stored procedures in Transact SQL (T-SQL).

Getting Data via a Stored Procedure
First, you need to build the database stored procedure. Using the Pubs database, create a procedure that accepts three input parameters and outputs a recordset. The three inputs consist of the search type, the main search argument (SARG), and a second SARG to filter the recordset by year-to-date (YTD) sales. Then, build an IIS/ASP page to capture user inputs and display the recordset.

Choosing the Search Types. The first procedure parameter is the type of search the user requested. Using a search type parameter provides flexibility in the types of searches a Web page allows. This simple procedure offers only three search types: the author's last name (Author), the title of a book (Title), and the author's ID (Author_ID). If no value is provided for the search type, use a default value of Author. And because browsers manipulate only strings, you need to use a varchar() data type to pass in all the variables.

As you can see in Listing 1, I named the search type variable @strSearchType. Using @strSearchType and T-SQL flow-of-control commands, the procedure chooses which Begin...End block to execute. Each block contains a different search (SELECT statement) to return a recordset to IIS/ASP. The T-SQL flow-of-control commands are limited, so use the IF-ELSE command. You won't win any coding contests, but after this search compiles, the server processes it quickly. Notice that I did not use a CASE statement. T-SQL has a CASE expression, but you can use it only within a statement (e.g., SELECT, UPDATE, DELETE).

Establishing the Search Arguments. The first parameter identifies which block to process; the next parameters provide search arguments that specify what to search for. For this project, I used two SARG types. The first accepts a string the user provides. Users enter a name or title. The second search argument lets users further limit their searches based on YTD sales for any book title. This project uses only two SARGs, but you can use any number or none at all. If you want to build an elaborate search procedure for your data warehouse, you can use up to 255 parameters in SQL Server 6.x (1024 in SQL Server 7.0).

The first SARG in the project accepts a string, such as an author's name or a book title. To enable users to search for a book without knowing the author's last name or the full book title, use a LIKE clause with a wildcard at the end of the string to expand the search. Use this command to add the wildcard:

SELECT @NameSrch = @strUSERINPUT + '%'

If you use the LIKE clause and wildcard combination, you won't lose query performance. The query optimizer still uses your indexes (a column used this way might be a good candidate for a clustered index). But if you add the % wildcard at the beginning of a LIKE argument, you force the optimizer to use a table scan, which can decrease performance. (For more information, see Dusan Petkovic and Christian Unterreitmeier, "New Features for Query Processing," July 1999.)

The next SARG in the project runs against the ytd_sales column in the Titles table. YTD sales are stored as a money data type, so you need to convert the incoming search parameter into the money type. Also, you need the procedure to validate that all incoming string characters are numbers. If they aren't, set the parameter default to zero. (Alternatively, you can use VBScript in IIS/ASP to validate that string characters are numbers.) To check for numbers, use a CASE expression such as this one:

SELECT @moneyYTDsales = CASE WHEN @strYTDsales LIKE '%[^0-9]%'  THEN 0
        ELSE CONVERT(money,@strYTDsales)
END

Using a LIKE clause when you want to know whether the string is not like a number might seem counterintuitive, but that's where the ^ symbol comes into play. In this command, LIKE '%[^0-9]%' resolves to TRUE if any character is not between 0 and 9.

The last objective for this procedure is to provide an HTML link to the author's name. To do so, include the HTML references as part of the author's name column returned with each record. This procedure creates a string with a value similar to: "<A HREF='PUBSlookup?author_id=363-45-1234'>author name</A>." The URL references the same page, so you begin HREF with just the ASP page name. Within a browser, you want to display the author's first and last names. The bottom status bar in Screen 1, page 48, displays the full hyperlink, which shows the author's ID.

To include a hyperlink to an author ID, you can use this T-SQL code:

SELECT Author
= SUBSTRING("<A HREF='pubslookup.asp?Author_id=" + 
	COALESCE(A.au_id,") + "'>" 
+ SUBSTRING(COALESCE((au_fname + ' ' + au_lname),'-
	'),1,25) + "</A>" , 1,75) ,

The final SUBSTRING length of 75 represents a number equal to or greater than the combined length of all characters returned, including the embedded HTML tags. Link tags are useful because they don't show up when you copy the data from a browser.

   Prev. page   [1] 2 3     next page