If OBJECTPROPERTY(OBJECT_ID('ParseCSVLists'), 'IsProcedure') = 1
	drop procedure ParseCSVLists
GO

/*****************************************************************************
	Author: Mark Caldwell
	Date:	08/19/02
	Desc:	Parse two correlated CSV lists into a result table

*****************************************************************************/

CREATE PROCEDURE ParseCSVLists
	@GameID	int,
	@ItemList	varchar(5000),
	@AnswerList	varchar(5000)
AS
	SET NOCOUNT ON

	-- Create working tables for input & output of parsing statement.
	DECLARE @TempSource TABLE (
		ListType	varchar(10),
		List	varchar(5000)
	)

	DECLARE @TempItemList TABLE (
		RecID	int	NOT NULL	IDENTITY(1,1),
		ListType	varchar(10),
		OneValue	varchar(100)
	)

	DECLARE @TempAnswerList TABLE (
		RecID	int	NOT NULL	IDENTITY(1,1),
		ListType	varchar(10),
		OneValue	varchar(10)
	)

	-- Insert lists passed in as parameters into source table.
	INSERT INTO @TempSource (ListType, List) VALUES ('Item', @ItemList)
	INSERT INTO @TempSource (ListType, List) VALUES ('Answer', @AnswerList)

	-- Use CSV Parsing logic from Rob Volk's article on SQLTeam (http://www.sqlteam.com/item.asp?ItemID=2652)
	-- First, do Items and assign a record number for joining to Answers later.
	-- Because programmer concatenates comma at end of string, REMOVE the end-comma concatenation from Rob's Code.
	INSERT INTO @TempItemList 
		SELECT 
			ListType, 
			NullIf(SubString(',' + List, ID , CharIndex(',' , ',' + List, ID) - ID) , '') AS OneValue 
		FROM Tally, @TempSource 
		WHERE ListType = 'Item' 
			AND ID <= Len(',' + List) 
			AND SubString(',' + List, ID - 1, 1) = ','
		
	-- Next, do Answers and assign a record number for joining to Itemss later.
	INSERT INTO @TempAnswerList 
		SELECT 
			ListType, 
			NullIf(SubString(',' + List, ID , CharIndex(',' , ',' + List, ID) - ID) , '') AS OneValue 
		FROM Tally, @TempSource 
		WHERE ListType = 'Answer' 
			AND ID <= Len(',' + List) 
			AND SubString(',' + List, ID - 1, 1) = ','

	-- Now Join the Items to their Answers and Insert into final Results Table with Game ID.
	INSERT INTO Results
		SELECT 
			@GameID,
			I.OneValue as Item, 
			A.OneValue as Answer
		FROM @TempItemList I
			JOIN @TempAnswerList A on I.RecID = A.RecID

	SET NOCOUNT OFF
GO

GRANT EXECUTE ON ParseCSVLists TO WebUser
GO