1

I need to insert a parameter into an IN condition for a particular SQL statement. The actual value of the parameter is a command separated list of ID's that could be 1 ID or multiple ID's. Here is the SQL;

                CommandText = @";WITH CTE(ListID, ParentID) AS
                                (
                                    SELECT ListID, ParentID FROM dvw.DistributionList WHERE ListID IN (@ids)
                                    UNION ALL
                                    SELECT dl.ListID, dl.ParentID FROM CTE AS c INNER JOIN dvw.DistributionList as dl
                                    ON dl.ListID = c.parentID
                                )

                                SELECT DISTINCT Email FROM tiger_dev.dvw.Subscriptions AS sub INNER JOIN dvw.Users AS u
                                ON sub.UserName = u.UserName WHERE u.ActiveUser = 1 and sub.ListID IN
                                (
                                    SELECT ListID FROM CTE
                                )
                                ORDER BY Email"

Here's the issue, The ListID column data type is int. When I run the statement I get some conflicting data type issues. Here's a sample one;

Conversion failed when converting the nvarchar value '4703,4724,4704,4705' to data type int.

Is there anyway to go about this without having to create potentially X amount of parameters in the SQL text and set each of them?

Mikolz
  • 33
  • 7
  • 1
    That's because you can't just stick a string in where a list of values should go. When you do that sql thinks you are passing an in but you passed in a whole delimited list. You will need to split those values using a splitter. https://sqlperformance.com/2012/07/t-sql-queries/split-strings You also are using the older ANSI-89 style joins. You really should use the "newer" ANSI-92 style joins, they have been around for over 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Sean Lange Oct 07 '15 at 19:35
  • 3
    Possible duplicate of [WHERE IN (array of IDs)](http://stackoverflow.com/questions/182060/where-in-array-of-ids) – STW Oct 07 '15 at 20:06
  • See the duplicate question for a good answer, as well as a workaround (if there is no alternative) – STW Oct 07 '15 at 20:07
  • Updated the SQL to use correct joins. Thanks for pointing that out. I link was useful and I ended up implementing one of those solutions. The SQL is working as expected now. – Mikolz Oct 08 '15 at 00:32

1 Answers1

0

Even though ListID is an int type, you need to use a varchar for the @ids variable/parameter to allow it to contain a comma-separated string.

Then you need to concatenate it to your dynamic sql string like this:

' ... WHERE ListID IN (' + @ids + ') ...'
Tamir Vered
  • 10,187
  • 5
  • 45
  • 57
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52