The fourth and final IF block determines whether 'Large' is the only option in the string. The fourth block is necessary for any table that allows a null value. The logic of the complete stored procedure is also necessary to avoid having a string full of rogue delimiters or misplaced commas that would confuse the other operations on this table. Executing the DeleteOption procedure sets the Options string for ItemID 100 to 'Small,Medium,Extra Large'.
A search of options is relatively straightforward. To serve the customer who wants to find all yellow items, you can execute
SELECT ItemName FROM AB2 WHERE Options LIKE "%yellow%"
This statement returns all items that have an option of yellow anywhere in the string. Surrounding the search word with the wildcard % accounts for commas and the variable placement of 'yellow' within the option string.
Abbey Someone
You might be thinking that working with AB2 has been relatively painless and that AB2 isn't too bad after all. But let's examine the T-SQL code in Listing 7, page 46, which you'd need to populate the drop-down list from AB2. First, compare the code in Listing 7 to the following T-SQL code that I used in the first example with two entities:
SELECT ItemOption FROM options WHERE
ItemId = 100
ORDER BY ItemOption
The difference is significant. The above simple SELECT statement would perform orders of magnitude faster than the GetAllOptions2 stored procedure from Listing 7. Let's break down the new stored procedure to see what makes it so cumbersome. You need several variables: @length, to hold the length of the options string plus 1; @position, to keep track of which character the SUBSTRING() function is currently examining; @add, which holds the contents of the options string one character at a time; @var, which holds the cumulative single characters that make up a word; and @list, which stores the whole string from the Options column.
The code at callout A in Listing 7 creates a temporary table to hold the options in rows as you build the options from the individual characters. At callout B, the code declares and initializes the variables. It sets @position to 1 and @list to the contents of the Options column in the row that has an ItemId that matches the @ItemId you pass to the stored procedure.
Using a WHILE keyword, the main block of the stored procedure loops until it has read every character from the original string. Callout C shows this first part of the main block, which determines whether the character it's currently analyzing is a comma or whether it's at the end of the string. If either condition is true, the code inserts the current contents of @var into the temporary table. The first character isn't a comma, so the code puts the character into the variable @add, then at callout D sets @var to itself plus the new character.
In this way, the procedure builds the individual options one character at a time. The code then increments the position so it can analyze the next character. The loop continues until it finds a comma, then the code inserts @var into the temporary table and clears that variable out so it can hold the next option. The procedure continues until it reaches the end of the string and each option exists as a row in the temporary table. When you have the options stored as rows in the temporary table, you can use the code that Listing 8 shows to loop through the query and populate the drop-down list.
Nobody's Perfect
The examples in this article demonstrate the benefits of using simple SQL statements and a normalized design instead of costly, time-consuming string operations. Like most database engines, SQL Server isn't optimized for the operations we've just done on the abnormal form tables. Performance suffers noticeably when you're using 1ANF tables like AB1 and AB2. Nonetheless, with the examples I've presented in this article, if you have absolutely no choice, you can effectively deal with some of the problems that tables in the first abnormal form present. Always remember that using 1ANF is very bad design practice. I implore you to never implement such a table, no matter how small you think your application will be. You don't want to fall victim to the headaches that unexpected growth causes.
End of Article
Prev. page
1
2
3
[4]
next page -->
