SQL IN Statement C# Parameter string holding more than one value

I want to read in a string value from an XML file that holds more that hold more than one group number. In my SQL script I have 2 lines that use the group number:

        AND AccountsPayable.CoverageGroupCode in (@grpno) 



 

 and


  
        WHERE dbo.Claims.GroupNbr in (@grpno)

Then after the script is set to the cmd.CommandText I have:

cmd.Parameters.AddWithValue("@grpno", groupno);




 

My XML file is read in fine and the line is:
 \\xxxxxxx\xxxxxx\Folder" GroupNo="005465','007284">

 
It runs and does not abort, but no records are found.  As you notice on my GroupNo, I do not incude the
leading single quote or the trailing single quote for GroupNo, because it looks like .NET provides them.
When I only have one GroupNo as:
   \\xxxxxxx\xxxxxx\Folder" GroupNo="005536">
it works fine.  No leading or trailing single quotes are provided.

 
The lead programmer I am sure wants me to use a parameter and not use a variable in the middle of the SQL
script.  I know it will work that way, but I would like to use a parameter.  I want to use one program to
run many extracts.  If not the lead programmer will want me to have a separate program for each extract 
I have many extracts.  How can I so this?
 
Is there a way with .NET and C# to see what exactly what the SQL script is run?  If I print out CommandText, 
it shows the @grpno in the script and not what is substituted from the XML file.



 
Thanks ahead of time for any help on this.






 
arep

SQL doesn't work well with bound variables that are meant to serve as the IN list - it rather binds the variable value (the whole list) as a single value and then operates just like it was a = operator rather than IN. You need some kind of a work-around for that. For example, I know that Oracle can sometimes be forced to work well by binding a table rather than a varchar variable.

Anyway, from my past experience, working with IN lists, whether trying to bind them or to hard-code them, is often not a good idea, simply because you can't control the length of the query if it is made like that. Similar problems exist in XPath, with similar outcomes, i.e. no nice solution...

If you're working with a proper SQL database, maybe you can work around the problem by creating a temporary table in which you would insert values of interest. Then select rows that have GroupNo present in that table. Not really a neat solution, but might suffice. Another equally bad solution is not to bind the variable but rather encode IN list manually. I did that also couple of times, works well and looks nasty. In that case you have to make sure that IN list does not breach some predefined length, e.g. 2.000 characters or so, just to make sure that SQL engine will not find the query too long.

Zoran

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
SQL doesn't work well with bound variables that are meant to serve as the IN list - it rather binds the variable value (the whole list) as a single value and then operates just like it was a = operator rather than IN. You need some kind of a work-around for that. For example, I know that Oracle can sometimes be forced to work well by binding a table rather than a varchar variable.

Anyway, from my past experience, working with IN lists, whether trying to bind them or to hard-code them, is often not a good idea, simply because you can't control the length of the query if it is made like that. Similar problems exist in XPath, with similar outcomes, i.e. no nice solution...

If you're working with a proper SQL database, maybe you can work around the problem by creating a temporary table in which you would insert values of interest. Then select rows that have GroupNo present in that table. Not really a neat solution, but might suffice. Another equally bad solution is not to bind the variable but rather encode IN list manually. I did that also couple of times, works well and looks nasty. In that case you have to make sure that IN list does not breach some predefined length, e.g. 2.000 characters or so, just to make sure that SQL engine will not find the query too long.

Zoran
0