How to Pass Comma-Separated String into Query

[bullfrog83]

I wrote a function that takes a comma-separated string, parses out the individual values, places quotations around each value and a comma in-between each value so that they can be processed in a query. This is my function:

CREATE FUNCTION dbo.fnParseCSV
(
@ExcludedCourses varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
	DECLARE @ParsedExcludedCourses varchar(255)
	DECLARE @ExcludedCourse varchar(10)
	DECLARE @Pos int
	
	WHILE LEN(@ExcludedCourses) > 0
	BEGIN
		SET @Pos = CHARINDEX(',',@ExcludedCourses)
		
		IF @Pos = 0
			BEGIN
				SET @ParsedExcludedCourses = ISNULL(@ParsedExcludedCourses,'') + '''' + LTRIM(@ExcludedCourses) + ''''
			END
		ELSE
			BEGIN
				SET @ExcludedCourse = '''' + SUBSTRING(@ExcludedCourses, 1, @Pos - 1) + ''''
				SET	@ParsedExcludedCourses = @ExcludedCourse + ',' + ISNULL(@ParsedExcludedCourses,'')
			END
			
		IF @Pos = 0
			BEGIN
				SET @ExcludedCourses = ''
			END
		ELSE
			BEGIN
				SET @ExcludedCourses = LTRIM(SUBSTRING(@ExcludedCourses, @Pos + 1, LEN(@ExcludedCourses) - @Pos))
			END
	END
	
RETURN @ParsedExcludedCourses
END
GO

This is how it works when utilized:

DECLARE @courses varchar(255)
SET @courses = 'COL 100,COM 100,MAT 100'

SELECT dbo.fnParseCSV(@courses)

Results: 'COL 100','COM 100','MAT 100'

However, if I try executing the following query using the function I get zero results:

DECLARE @courses varchar(255)
SET @courses = 'COL 100,COM 100,MAT 100'

SET @courses = dbo.fnParseCSV(@courses)

SELECT *
FROM Courses
WHERE CourseId IN (@courses)

But if I execute the query as such I get results:

SELECT *
FROM Courses
WHERE CourseId IN ('COL 100','COM 100','MAT 100')

I am at a loss as to why it's not working. Any help is much appreciated!

[gpl]

You have to return a table value -- here is an example for returning a set of ints

CREATE FUNCTION dbo.CsvToTable (@Array VARCHAR(1000))
RETURNS @IntTable TABLE (IntValue int)
AS
BEGIN
	DECLARE @Separator Char(1)
	DECLARE @SeparatorPos int
	DECLARE @ArrayValue Varchar(1000)
	
	SET @Separator = ','
	SET @Array = @Array + ','
	
	WHILE PATINDEX('%,%', @Array) <> 0
	BEGIN
		SET @SeparatorPos = PATINDEX('%,%', @Array)
		SET @ArrayValue = LEFT(@Array, @SeparatorPos -1)
		
		INSERT @IntTable VALUES(
			CAST(@ArrayValue AS INT))

		SET @Array = STUFF(@Array, 1, @SeparatorPos, '')
	END
	
	RETURN
END

[NeoPa]

As gpl says, the IN() structure takes either a list of items, or a table/cursor. To help understand, what you are trying to do is equivalent to :

SELECT *
FROM   [States]
WHERE  [StateAbb] IN('''IN'',''OH'',''NY''')

but you are really looking for a last line of :

WHERE  [StateAbb] IN('IN','OH','NY')

Multiple (list of) strings rather than a string formulated to contain strings.

Does that clarify?

[bullfrog83]

OK, I understand now. The table function works very well and I like it better than what I had.

[NeoPa]

Glad that helped :)

I agree about the table thingy too. I'm just getting into SQL Server myself from many years in Access. This is very much a server style thing (returning tables etc) and something I have to consider more going forward. I'm enjoying the extra scope and learning about capabilities just not available to Access work.