SQL Server has no built-in way to deal with comma-separated lists. The first part of this series showed how you can pass a comma-separated list to SQL Server and parse the list into individual items to store in your database tables. This part will show how you can display that data and update it as well.

Joins

The complexity of multiple categories for products is dealing with a many-to-many relationship. Instead of having one category per product (a one-to-many relationship, as one product can have one category, but each category can be related to many products), we are dealing with a pass-through table that joins many products to many categories (one product can have multiple categories, and one category will be related to multiple products.) The functionality is managed in the joins — how do we join the 2 unrelated tables Products and Categories using the third table ProductsCategories? The SQL statement is actually pretty easy. You have to create a join between Products and ProductsCategories, and create a join between Categories and ProductsCategories. We can do that all in one statement:

SELECT p.ProductName, p.ProductID, c.CategoryName, c.CategoryID
FROM Products p
INNER JOIN ProductsCategories pc
ON p.ProductID = pc.ProductID
INNER JOIN Categories c
ON c.CategoryID = pc.CategoryID
ORDER BY c.CategoryName

This statement will give us a list of ALL products in the Products table (assuming each product had at least one related category). If we put this on a web page, we can then set up a dynamic table (a Dreamweaver Application object located in the Insert menu) and display the data (DisplayProducts.cfm and DisplayProducts.asp in the download at the end of the article). The data will show one row for each category for each product — in other words, one product with 3 categories would display as 3 rows of data:

Tip: There are several ways to organize the page so that the "nested region" (the product name) is not shown more than once. One way is to use the Simulated Nested Region server behavior, available for free download at www.tom-muck.com.

This type of display might not suit you. What if you want to display each product on one row and show a category list? One way to do it would be with two recordsets. The first recordset would be a simple SELECT * FROM Products. You would set up a dynamic table and then nest another recordset inside the dynamic table loop showing categories for each individual product with ANOTHER loop to output the categories. This is convoluted, tedious, and a drain on server resources. There has to be a better way, and in fact, there is.

Functions

There is a better way, using another comma-separated list — this time a list that is created by SQL Server. We'll use a SQL Server User-Defined function. If you've never used a function in SQL Server before, this will be a good one to start with because you'll find a lot of uses for it.

To create a function, simply open the Query Analyzer and type your function code, or use the Enterprise Manager and right-click on User-Defined Functions > New User-defined Function. The basic syntax for a function in SQL Server is:

CREATE [function name] ([parameters])

RETURNS [return type]

AS

BEGIN
[FUNCTION BODY]
RETURN [parameter]
END

The code you'll use for this tutorial is as follows:

CREATE FUNCTION fn_ProductCategories(@ProductId int)

RETURNS varchar(255)

AS

BEGIN

DECLARE @CategoryList varchar(500)

SELECT @CategoryList = COALESCE (@CategoryList + ',', '') + c.CategoryName
FROM Categories c INNER JOIN
ProductsCategories pc ON c.CategoryID = pc.CategoryID
WHERE (pc.ProductID = @productid)

RETURN @CategoryList

END

The function uses a little-used built-in SQL keyword called COALESCE. The COALESCE function returns the first non-null value, as in this statement:

SELECT COALESCE (null,null,null,'goose') as Sample

In this simplified example, the first 3 values are nulls, so the 4th value is placed into Sample. If you run it in the Query Analyzer, you'll see the output "goose". In our user defined function, the SELECT statement is recursing over the @CategoryList variable. Our COALESCE function has two parameters — @CategoryList + ',' and an empty value ''. When the first row is returned, the @CategoryList variable is NULL — the first non-null value within the parenthesis is the empty value, which is appended to the first CategoryName value from the table: @CategoryList will now contain the first category found in the SELECT query. Each succeeding row will be appended to the variable along with a comma (the first non-null value in the COALESCE function). When the SELECT statement is complete, the @CategoryList variable contains a comma-separated list of category names, which is returned to the calling query.

A function can be called from any query. This function takes a ProductID as a parameter, so we'll write a query that retrieves ProductName, ProductID, and the category list as CategoryList like this:

SELECT ProductName, ProductID,
dbo.fn_ProductCategories(ProductID) as CategoryList
FROM Products

The query returns the results like this:

The query could have been easily written as a stored procedure as well:

CREATE PROCEDURE spGetProducts
AS
SELECT ProductName, ProductID,
dbo.fn_ProductCategories(ProductID) as CategoryList
FROM Products

Updating Data

For the update portion of this tutorial, we'll combine both techniques: getting the comma-separated list from the database, using it to display the data, and then updating the database with the comma-separated list as well.

Updating will be a little more complex. You are not updating the Products table or the Categories table with the CSV list — you are deleting/inserting data into the ProductsCategories table. Imagine this scenario: you have a product that is related to 5 different categories. You want to update it to remove one category and add one category. How do you do it? One school of thought is to do it in these steps:

1. Pass a CSV list to a stored procedure.
2. Loop through the categories that are already stored in the database under that product and then delete any that are not in the CSV list.
3. Loop through the CSV list and insert them if they don't already exist.

This is a good method, although the scripting is a little more complicated than the following method. This next method also uses 3 steps but does not require the looping in step 2, or the comparisons which slow down the operations. These are the steps we'll use to update the multiple categories for each product:

1. Pass a CSV list to a stored procedure.
2. Delete all the entries in the ProductsCategories table related to that specific product with a simple delete statement.
3. Do an insert using code from the stored procedure we already created in part 1.

In fact, what we'll do is modify the stored procedure that we wrote in the first part of this series so that it works for inserts and updates both. The code for the stored procedure is below, with the new code highlighted that will allow the procedure to do updates and inserts:

CREATE PROCEDURE spInsertProduct

@productname varchar(255), -- Parameter is the product name
@categorylist varchar(255) -- Parameter that is our category list
,@productid int = 0 -- Our product id if this is an update statement
-- We set a default value of 0 so that an insert statement does not
--   need to pass in this parameter

AS

-- First, insert product name. We'll need to retrieve product id
--DECLARE @productid int (don't need this any more, so commented out)

IF @productid = 0 BEGIN -- if this is insert, do the insert
INSERT Products (ProductName) VALUES (@productname)
-- Get the identity value into the @productid variable
SELECT @productid = @@identity
END
ELSE BEGIN -- ProductID not 0, must be an update
UPDATE Products SET ProductName = @productname
WHERE ProductID = @ProductID

-- Delete entries for this product
DELETE FROM ProductsCategories WHERE ProductID = @productid
END


-- Next, do the list

- - Declare an index variable for looping
DECLARE @index int

-- Declare a variable to hold a single CategoryID
DECLARE @CategoryID varchar(10)

-- The @categorylist variable will be pulled apart at the commas
-- until nothing is left
WHILE @categorylist is not null

-- Start the loop
BEGIN

-- Find the comma
Select @index = CHARINDEX(',', @categorylist)

-- if there is no comma, must be finished
if @index = 0
BEGIN
  -- Get the last item and set @categorylist to null to end loop
  SELECT @CategoryID = ltrim(rtrim(@categorylist))
  SELECT @categorylist = null

END

-- Not last item
ELSE BEGIN
  -- Set the @CategoryID variable to element up to comma
  SET @CategoryID = ltrim(rtrim(LEFT(@categorylist, @index-1)))
  -- Remove item from string. @categorylist becomes shorter
  SET @categorylist = RIGHT(@categorylist,LEN(@categorylist) - @index)
END

-- Insert/Update goes in place of PRINT
--PRINT @CategoryID
INSERT ProductsCategories (ProductID, CategoryID)
VALUES (@ProductID, @CategoryID)

-- Loop is ended
END
GO

The next step is to modify the form page so that it works for inserts and updates. Recall that we had two separate pages: a form and the action page. On the form page (AddProduct.cfm and AddProduct.asp) we displayed a list of categories fed by a recordset. We'll make 4 small changes to the page to make it work as an update page (described briefly here, but the sample files can be found in the download package at the end of the article):

1. Change the categories recordset.
2. Add a product recordset.
3. Add default values to the form fields.
4. Add a hidden field named ProductID.

We'll change the rsCategories recordset to add the new field name Selected to hold a value of the ProductID or NULL. We will list all categories in the list and select only those categories that apply to a specific product (the Selected field will have a value of ProductID if there is a match, and NULL if not). The SQL will compare two lists: the Categories table, and the Categories table filtered by the ProductID field from the ProductsCategories table. To do this, we'll need an outer join that will join Categories with a sub-select of only those categories that are linked to a specific product. Because we are using an outer join like this, if we don't have a current product, the query will work just as well and no item will be selected. For that reason, we can use this on a combination insert/update page.

Tip: A sub-select (SELECT statement within another select statement) is like a virtual table and can be used in joins if you give the sub-select statement a table alias.

The SQL code (built into a stored procedure) is as follows:

CREATE PROCEDURE spGetCategories

@ProductID int

AS

SELECT AllCat.CategoryName, AllCat.CategoryID,
ProductID as Selected FROM Categories AllCat
LEFT OUTER JOIN
(SELECT c.CategoryID, pc.ProductID FROM Categories c
INNER JOIN ProductsCategories pc
ON c.CategoryID = pc.CategoryID
WHERE pc.ProductID = @ProductID) CatList
ON AllCat.CategoryID = CatList.CategoryID
ORDER BY AllCat.CategoryID

The Categories table has the alias AllCat, and the sub-select that filters the categories has the table alias of CatList. We do an outer join on these tables, and use the field alias of Selected to return either a ProductID if there is a match, or a NULL value if there is no match. Call this stored procedure to supply the values to the select list on the page.

The rsProduct recordset needs to be created using this SQL:

SELECT * FROM Products WHERE ProductID = [productid]

When you create the recordset, you'll filter it by a URL parameter named ProductID.

The textfield named Product needs to have a default value of rsProduct.ProductName. A hidden field named ProductID needs to be created with a default value of url.productid (or Request.QueryString("ProductID") in ASP.)

Finally, the Category select list needs a bit of conditional logic in the option tag to handle the selected items:

ColdFusion

<cfif rsCategories.selected NEQ ''>selected="selected"</cfif>

ASP

<%If CStr(rsCategories("Selected")) <> "" Then
Response.Write("selected=""selected""")
End If %>

With the AddProduct page completed, we can modify the Insert.cfm or Insert.asp file to add the ProductID parameter to the call to the stored procedure. The completed pages are in the download zip at the end of the article.

Conclusion

This 2-part series showed a few methods of working with CSV strings, and the associated data insertion, retrieval, and updating. You learned about the many-to-many relationship, the User-defined function, the COALESCE function, and using conditional logic in a stored procedure to allow the stored proc to serve double duty as an insert and update stored procedure.