How to do a SQL select statement with where clause is in csv format?

Question

I am passing in a dynamic number of id's in csv format as a sql parameter and want to use them in a where clause for a select statement.

I can't get my head around how to do this in sql.

Pseudocode would look something like this:

@ids varchar(max)

select stuff from table
where stuff =  [email protected](',')

Any help would be greatly appreciated.

ok after a bit of research i think that the best way to do this is with xml rather than csv's since it will be less effort so I am going to select the best answer and close this. Thanks guys.

... That's going to depend on your DB, application language, and connection driver, none of which you've provided. Often, though, you can't just provide an array (perhaps in an effort to encourage JOINs?) - you either have to list a specific number of parameters, or construct the query dynamically (more difficult, and a little dangerous). — Clockwork-Muse
– Clockwork-Muse, Commented Jul 2, 2013 at 22:27

Zef · Accepted Answer · 2013-07-02 22:40:04Z

3

If I understand correctly that you want to pass a dynamic list then in SQL Server create a table valued function:

CREATE FUNCTION ParseIds (@ids varchar(MAX)) RETURNS @TempIds TABLE (Id INT)
AS
BEGIN
    DECLARE @Pos INT 
    SET     @Pos = 1

    SET     @ids = ',' + @ids + ','

    WHILE   @Pos < LEN(@ids)
    BEGIN
            INSERT  @TempIds 
            VALUES (Convert(int, SUBSTRING(@ids,@Pos+1,CHARINDEX(',',@ids,@Pos+1)-@Pos-1)))

            SET     @Pos = CHARINDEX(',',@ids,@Pos+1)
    END

    RETURN
END

Pass your dynamic CSV Id list into your stored proc and use in query like this:

select stuff from table
where stuff IN (SELECT id FROM dbo.ParseIds(@ids))

answered Jul 2, 2013 at 22:40

Zef

1091 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin M · Accepted Answer · 2013-07-02 22:24:46Z

0

the IN query does what you need:

SELECT stuff FROM table WHERE stuff in (1,4,7,9)

Consider SQL-Injection if you constuct the SQL from insecure sources (@ids entered by users etc.)

answered Jul 2, 2013 at 22:24

Martin M

8,6883 gold badges40 silver badges57 bronze badges

1 Comment

Ken White Over a year ago

That does not work when passing a string variable (@ids varchar(max)) to the IN statement. It has to be parsed.

Collectives™ on Stack Overflow

How to do a SQL select statement with where clause is in csv format?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related