Debug stored procedure that uses comma delimited list to insert multiple records

I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:

exec proc_Add_Product_Files @.FileName = N'This is just a test.doc', @.FileDescription = N'test', @.FileSize = 24064, @.LanguageID = NULL, @.DocumentCategoryID = 1, @.ComplianceID = NULL, @.SubmittedBy = N'Kevin McPhail', @.SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @.ProductID = N'10,11,8'

Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)

CREATE PROCEDURE proc_Add_Product_Files_v2
/*
Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a row
into productfiles for each product that the file being uploaded pertains to.
*/
@.FileName varchar(150),
@.FileDescription varchar(150),
@.FileSize int,
@.LanguageID int,
@.DocumentCategoryID int,
@.ComplianceID int,
@.SubmittedBy varchar(50),
@.SubmittedDate datetime,
@.ProductID varchar(150)

AS
BEGIN

DECLARE @.FileID INT

SET NOCOUNT ON

/*
Insert into the files table and retrieve the primary key of the new record using @.@.identity
*/
INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate)
Values
(@.FileName, @.FileDescription, @.FileSize, @.LanguageID, @.DocumentCategoryID, @.ComplianceID, @.SubmittedBy, @.SubmittedDate)

Select @.FileID=@.@.Identity

/*
Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.
*/
DECLARE @.ProductFilesInsert varchar(2000)

SET @.ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@.FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @.ProductID + ')'

exec(@.ProductFilesInsert)

End
GO

I created your stored procedure locally, and did a PRINT of @.ProductFilesInsert and all looks good to me. Setting @.FileID = 0 instead of selecting its value to be @.@.Identity, this is what @.ProductFilesInsert contains, and that is syntactically correct:

INSERT INTO ProductFiles (FileID, ProductID) SELECT 0, Product1ID FROM Products WHERE Product1ID IN (10,11,8)

Your stored procedure is named proc_Add_Product_Files_v2, yet you are executing proc_Add_Product_Files. Is the problem simply that your are executing an old version of your stored procedure?|||

Terri:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

Thanks again,

Kevin

|||

Kevin.McPhail wrote:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

It wasn't obvious to me. The only reason I noticed was that exec proc_Add_Product_Files failed failed for me because I didn't have the original in place :-) I can't tell you how many times I've been burned by the very same thing.

For what it's worth, I am not a big fan of dynamic SQL, especially when an alternate methodology is possible. You could use this approach instead:

INSERT INTO
ProductFiles
(
FileID,
ProductID
)
SELECT
@.FileID,
Product1ID
FROM
Products
INNER JOIN
dbo.Split(@.ProductID,',') AS A ON Products.Product1ID = A.Element

There are many variations of a "split" function. Here's one that Dinakar provided in this thread:http://forums.asp.net/989365/ShowPost.aspx:

CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END|||

Thanks again Terri! I had been looking for a good understandable (not a sql guru) way to pass a delimited string or array to sql for inserts. I read through a couple articles i found that left my head spinning and decided to go with the old dynamic sql method since i at least understood what it did. Your example(and Dinakar and Karen's ) is exactly what i had been looking for.

Thanks,

Kevin