There are many times when you want to send a comma-separated list to a SQL Server stored procedure for inserting, updating, or searching data. The SQL language is a bit primitive for those of you who have experience with looping through lists in other languages, but it can be done. This tutorial will assume that you are using SQL Server with either ASP or ColdFusion, and have some experience with stored procedures. If not, Community MX has several good introductions to building stored procedures:

Moving to Stored Procedures - An Introduction

Moving to Stored Procedures - Part 2

Moving to Stored Procedures - Part 3

Stored Procedures Basics

Why is it useful?

If you can't think of why you might want to pass a CSV string to a database, picture the following scenarios:

• You have a blog and want to insert an item that will be seen in multiple categories.
• You have a job search application and you want to search several categories of jobs.
• You want to add a bunch of addresses to a letter campaign.
• You want to insert multiple records into one table.

One thing you almost never want to do is to store a comma-separated list into a database field. Why not? Because it becomes very tricky to update the data, and more importantly, it becomes hard to join the data on any of the values in the comma-separated list. Take this situation as an example. You have a Products table for an e-store that lists the following (ProductName and ProductID):

Beets,1
Peas,2
Carrots,3
Lettuce,4

Now you have a Category table like this (CategoryName and CategoryID):

Vegetables,1
Canned,2
Frozen,3
Fruit,4
Fresh,5

Your tables look like this:

Products

ProductID
ProductName

Categories

CategoryID
CategoryName

The inexperienced SQL programmer would be tempted to store a third column in the Products table to show the list of categories:

That looks like it should work, but now what happens when you want a list of all items in the Frozen category (CategoryID 3)? You would have to do scripting or SQL gymnastics in order to find out the information.

The correct way to do this is to create a third table that lists the ProductID and associated CategoryID:

ProductsCategories

ProductsCategoriesID
ProductID
CategoryID

This table would show the following:

Now you can find how many items are in each category easily. The ProductsCategoriesID field is simply there as a primary key to allow you to perform updates and deletes to the data. A primary key could also have been made up of the two other columns as a composite key, but that's beyond the scope of this article.

What about inserting or updating the list though? This is where the CSV handling comes in. One option is to do a batch insert using server-side scripting. This becomes cumbersome:

//psuedo-code
<%
for i = 0 to total items in list
    insert into database (field) values (list(i))
Next
%>

I'll show you how to pull the data apart in the stored procedure to make the database insert quick and painless, with a minimum of server-side code.

The Insert and Update page

We'll use the previous tables as an example and show how you might insert the data into the database using the CSV parsing. First, create the 3 tables using the Query Analyzer (the code is also listed as csvinsert.sql in the support download accompanying this article):

CREATE TABLE Categories (
 CategoryID int IDENTITY (1, 1) NOT NULL ,
 CategoryName varchar (50) NULL
)
GO

CREATE TABLE Products (
 productid int IDENTITY (1, 1) NOT NULL ,
 productname varchar (50) NULL
)
GO

CREATE TABLE ProductsCategories (
 ProductsCategoriesID int IDENTITY (1, 1) NOT NULL ,
 CategoryID int NULL ,
 ProductID int NULL
)
GO

Next, insert some data into Categories:

INSERT Categories (CategoryName) VALUES ('Vegetables')
INSERT Categories (CategoryName) VALUES ('Canned')
INSERT Categories (CategoryName) VALUES ('Frozen')
INSERT Categories (CategoryName) VALUES ('Fruit')
INSERT Categories (CategoryName) VALUES ('Fresh')

Next, set up a form page in Dreamweaver named AddProduct.cfm or AddProduct.asp, depending on your server model:

1. Put a recordset on a page and name it rsCategories, using this SQL in the advanced recordset dialog box:

SELECT * FROM Categories ORDER BY CategoryID

2. Add a form to the page

3. Add a text field named Products, a list named Categories (a multiple select list), and a submit button. The code should look something like this (form action is set to insert.cfm or insert.asp which will be created later):

<form name="form1" method="post" action="insert.cfm" >
<p>
Product Name: <input type="text" name="Product" />
</p>
<p>
Categories: <select name="Category" size="4" multiple="multiple">
<option value="0">* select categories * </option>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Submit" />
</p>
</form>

4. From the server behaviors menu, choose Dynamic Form Elements > Dynamic List/Menu. Choose the rsCategories recordset to populate the list, CategoryID for the value, and CategoryName for the label.

This is the beginning of the insert page. When the user clicks the submit button, the data will be passed to an action page. The product name will be passed in the Product form field, and the Category form field will contain a comma-separated list of category ids. Rather than loop through these categories, we'll pass the product name AND list of ids directly to the database stored procedure named spInsertProduct. First, let's test some looping code in the Query Analyzer. The looping code is commented inline:

-- Declare a variable that will be our CSV list
DECLARE @categorylist varchar(255)
SET @categorylist = '3,2,4,5'

-- 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 the element up to the comma
  SET @CategoryID = ltrim(rtrim(LEFT(@categorylist, @index-1)))
  -- Remove item from string. @categorylist becomes shorter by one item
  SET @categorylist = RIGHT(@categorylist,LEN(@categorylist) - @index)
END

-- Print the item to the Query Analyzer.
-- After the code is tested, this is where the
   --Insert/Update will go
PRINT @CategoryID

-- Loop is ended
END

If the code works, you should see the list values printed out in a column to the screen. If you change the list, you can see that the loop is working properly for the new list. The next step is to transform this into a stored procedure. The @categorylist variable will be passed to the stored procedure instead of being hard-coded. Also, we'll pass the product name to the stored procedure as well. This will be inserted first, followed by the looping code to insert each of the category id numbers. In place of the PRINT statement will be the category insert code. The stored procedure code is commented below:

CREATE PROCEDURE spInsertProduct

@productname varchar(255), -- Parameter is the product name
@categorylist varchar(255) -- Parameter that is our category list

AS

-- First, insert product name. We'll need to retrieve product id
DECLARE @productid int

INSERT Products (ProductName) VALUES (@productname)
-- Get the identity value into the @productid variable
SELECT @productid = @@identity

-- 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

Next, we'll create the action page to do the insert. For simplicity, we'll create a separate action page named Insert.cfm or Insert.asp, depending on your server model. Add a stored procedure/command to the page using the Bindings panel. The parameters (not Page parameters) should be set up using the form fields from the previous page as their runtime values:

ColdFusion

• @productname should use form.product
• @categorylist should use form.category

ASP

• @productname should use Request.Form("Product")
• @categorylist should use Request.Form("Category")

At this point you should be able to run the AddProduct page and insert a new product with multiple categories into the database.

Conclusion

This article showed how to pass a list to a stored procedure for a database insert. The next part of the series will show how to display the data after you have inserted it, and also how to update the data.