RE: How to pass param for IN() clause to sql command?
- From: Andrew Backer <awbacker@xxxxxxxxx>
- Date: Tue, 15 Jan 2008 18:08:01 +0000 (UTC)
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
Andrew,
This article covers a lot of the options:
http://www.sommarskog.se/arrays-in-sql-2005.html
Kerry Moorman
"Andrew Backer" wrote:
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.
// Andrew
.
- References:
- RE: How to pass param for IN() clause to sql command?
- From: Kerry Moorman
- RE: How to pass param for IN() clause to sql command?
- Prev by Date: Re: How to pass param for IN() clause to sql command?
- Next by Date: Nullable Foreign Keys
- Previous by thread: RE: How to pass param for IN() clause to sql command?
- Next by thread: SQL Questions: Attaching Databases to SQL Server
- Index(es):
Relevant Pages
|
