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.