How to pass param for IN() clause to sql command?

  • Thread starter Thread starter Andrew Backer
  • Start date Start date
A

Andrew Backer

How can I pass something that I want to use as a paramter in the "in" clause
of a straight sql text query?

I have a SqlCommand that is of type .Text, and contains something like this"
.....SELECT * FROM dbo.Table WHERE date_id IN ( @date_id_list )
..... // date_id is an integer

I have tried passing this in: < 1','2','3','4 >, where there is a 'quote/comma/quote'
between each entry. This should at least put no single quote at the start
& end.

I suppose it is being helpful and "fixing" my quotes to double quotes <''>,
rendring my trickerly useless.

Is there a way to do this so that the SqlCommand won't do exactly what it
should be doing (sanitizing my input) on this parameter? I need IN clauses
a lot for this bit. In my case I am using a regular DataSet & DataAdapter,
so anything that fixes it there is even better, though I am not afraid to
modify the code. If I have to move to external queries, then I will, but
it would be nice to keep it all in the dataset. Right now, this is the only
thing stopping me.

// Andre
 
Yep, that's an achiles heel of parameters passing.
One way would be to constuct a parametrised statement, like:
..... IN (@Param1, @Param2....
and dynamicall add all of the parameter values to Parameters collection.
 
Aye, so it's really not possible. I was just hoping that I couldn't google
the solution since it used the word "IN". Custom query runner it is then,
since I can't tell how many values they will select for the "in" clause.

Thanks,
//Andrew
 
Miha said:
Yep, that's an achiles heel of parameters passing.
One way would be to constuct a parametrised statement, like:
.... IN (@Param1, @Param2....
and dynamicall add all of the parameter values to Parameters collection.
Click to expand...
Ewww.... there's a better solution... albeit a bit of a kludge...
What we've done to overcome this problem is to ensure the data is
delimited somehow (pipes or comas), then loop through the string,
extracting each one, and putting it into a temp table (or a table
variable). Then we use that as the IN clause.

-ca
 
Yep, there are other ways as well.
But what I can't understand is why MS (and other database vendors as well)
don't provide a more friendly mechanism of passing arrays around, like
passing a normal parameter.
Afterall this requirement isn't something exotic - it is something that we
code every day again and again.
 
I've noticed that too. It seems like some of these issues are so preditcable
that it's surprising they didn't include them. Sometimes it's as if they
didn't use their own product, especially with some other ones. I can understand
this issue a bit, and how it might be difficult to say exactly what this
means, but stil... we have 100 other datatypes with custom handling, why
not one more =)

// Andrew
 
Great article, and glad to see that he/she/it is still around.

In the end, I can't write stored procedures since I am only allowed to report
off the database, not modify it. I have to use SSRS, so that forces me to
use DataSets. Ideally there would be a way to force the dataset's generated
code to deal with an array (or hack it to). Since this is a SqlCommand at
it's heart, one can hope. Modifying.augmenting the generated code turns
out to be way to much of a maintenance and clarity problem.

In the end, I came up with this:
* Xml "Query" schema which supports parameters of type "Any" and "In" and
a few others
* Individual files which contain the queries, and indicate which datatable
they should "fill"
* Use XSD to generate classes for the Query.xsd file (It doesn't use use
List(of T) for collections! )
* Loaders/etc to get them as Resources from the dll/deserialize/valiadate/etc
* A few shims to execute these, rename the tables they produce, and stick
them into RS

So far it works. The format is pretty simple, just <query><params><param
name=".." type="..."></><sql>blahbla @param blah blah</></> with a few other
attributes here and there.

I didn't want to got his far, but at least now the process for creating it
is straightfoward. Now, if only I could take these individual query files
and generate the DataSet from them easily, and then modify the definitions
contained in my SSRS report files!

Thanks again all,

//Andrew
 
Back
Top