Using COALESCE to Build Comma-Delimited StringBy Garth Wells on 1 February 2001 | Tags: Application Design Garth is back with another article. This one talks about building a comma-separated value string for use in HTML SELECT tags. It's also handy anytime you need to turn multiple records into a CSV field. It's a little longer and has some HTML but a good read.
I was reading the newsgroups a couple of days ago and came across a solution
posted by Itzik Ben-Gan I thought was really smart. In order for you to
understand why I like it so much I have to give you a little background on the
type of applications I work on. Most of my projects for the past 2.5 years have
focused on developing browser-based applications that access data stored in SQL
Server. On almost all the projects I have worked on there has been at least one
Add/Edit screen that contained a multi-select list box.
For those of you with limited experience working with HTML, I need to explain
that the values selected in a multi-select list box are concatenated in a
comma-delimited string. The following HTML creates a multi-select list box that
displays retail categories.
If a user selects more than one option the value associated with RetailCategory is a comma-delimited string. For example, if the user selects Shoes, Women's Clothes and Toys, the value associate with RetailCategory is 1, 4, 5. When the user Submits their form I call a stored procedure to insert the data into the appropriate tables. The comma-delimited string is processed with a WHILE loop and the individual values are inserted into a dependent table. Now that we have covered the Add part, let's take a look at what happens when a
user wants to Edit a row. When editing a row, you need to populate the form with
the existing values--this includes making sure all the multi-select list box
values that are associated with the row are shown as selected. To show an option
as selected, you use the "selected" attribute. If we were editing the row
associated with the previous example the final HTML would look like the
code shown here.
I say final, because the actual HTML is built on-the-fly using VBScript. To
determine which options are shown as selected, you must return a comma-delimited
string to IIS so you can manipulate it with VBScript. I use the Split function
and a For loop to determine which options should be shown as selected. The
following VBScript shows how this is done.
The remainder of this article shows the inefficient way I used to build the string along with the new, efficient way I learned from the newsgroup posting. The Old, Inefficient ApproachLet's create and populate some tables so we have some data to work with. Assume
you have a sales effort management (SEM) system that allows you to track the
number of sales calls made on a potential client. A sales call is not a phone
call, but a get together such as lunch or another type of person-to-person
meeting. One of the things the VP of Sales wants to know is how many of his
sales personnel participate in a call. The following tables allow you to track
this information.
A limited number of columns are used in order to make this article easier to
digest. The SalesCallsEmployees table is a junction table (aka associative
table) that relates the employees (sales personnel) to a particular sales call.
Let's populate the tables with sample data using these INSERT statements.
The first sales call (Lunch w/ John Smith) had three employees participate.
Using the old approach, I used the code shown here (inside a stored procedure)
to build the comma-delimited string. The resultset shows the output when the
"Lunch w/ John Smith" sales call is edited.
--Results--
This code may look a little complicated, but all it's doing is creating a cursor that holds the Emp_UniqueID values associated with the sales call and processing it with a WHILE to build the string. The important thing for you to note is that this approach takes several lines of code and uses a cursor. In general, cursors are considered evil and should only be used as a last resort. The New and Improved ApproachThe new and improved approach can create the same resultset with a single SELECT
statement. The following code shows how it's done.
--Results--
The COALESCE function performs the magic here. When @EmployeeList is NULL (the first row processed), it returns an empty string. On subsequent rows, it concatenates the @EmployeeList value with a comma and the current @Emp_UniqueID value.
|
- Advertisement - |