Dynamic SQL Comma-Delimited Value Query

sql dynamic-sql delimiter delimited
Oliver S picture Oliver S · Feb 5, 2009 · Viewed 8.8k times · Source

[Update: Using SQL Server 2005]

Hi, what I want to do is query my stored procedure with a comma-delimited list of values (ids) to retrieve rows of data.

The problem I am receiving is a conversion error:

Conversion failed when converting the varchar value ' +
@PassedInIDs + ' to data type int.

The statement in my where-clause and error is:

...
AND (database.ID IN (' + @PassedInIDs + '))

Note: database.ID is of int type.

I was following the article at:

http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

but did not complete because of the error.

In my execution script I have:

...
@PassedInIDs= '1,5'

Am I doing something wrong here? Thank you for your help.

Answer

Tom H picture Tom H · Feb 5, 2009

I would strongly suggest that you use the second method from that link. Create a user-defined function that turns your comma-delimited string into a table, which you can then select from easily.

If you do a Google on Erland and "Dynamic SQL" he has a good writeup of the pitfalls that it entails.

Related questions

Declare Variable for a Query String

I was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, …

Read More
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition) I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird. …

Read More
Drop all tables whose names begin with a certain string

How can I drop all tables whose names begin with a given string? I think this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.

Read More