Re: DataAdapter Parameter question by William
William
Tue Nov 11 13:38:46 CST 2003
I doubt it. The way the query processor works to build an efficient plan
dictates that it know what's in the IN clause.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Darwin S." <dar@xmission.com> wrote in message
news:13eb01c3a87c$cf7be440$a501280a@phx.gbl...
> Yes, I was afraid that was the case. It would be nice if
> such functionality existed. Maybe in the future?
>
> Thanks,
> Darwin
>
> >-----Original Message-----
> >Ah, it would be nice if this worked--it doesn't. The IN
> clause cannot accept
> >a parameter argument--it must be present when the
> procedure is syntax
> >checked. The only way to use this approach is to use
> EXEC SQL to recompile
> >the code at runtime or simply build the entire SELECT in
> your application
> >including the IN clause.
> >
> >--
> >____________________________________
> >William (Bill) Vaughn
> >Author, Mentor, Consultant
> >MVP, hRD
> >www.betav.com
> >Please reply only to the newsgroup so that others can
> benefit.
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >__________________________________
> >
> >"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in
> message
> >news:1sx%23kk$pDHA.1332@cpmsftngxa06.phx.gbl...
> >> Hi Darwin,
> >>
> >> In TSQL, we can write a enumeration in the IN clause.
> Such as: SELECT *
> >> FROM Employees WHERE EmployeeID IN (1, 3, 5). The
> enuerated values are
> >> separate by comma. If the values are string type, they
> have to be included
> >> in single quotes.
> >>
> >> So I think you don't need to use Sql parameters to do
> this. You can simply
> >> combine the values in to a string like the following:
> (If the value is
> >> string type, single quotes have to be added.)
> >>
> >> string strQuery = "SELECT * FROM Employees WHERE
> EmployeeID IN (" +
> >> value1.Tostring() + ", " + value2.Tostring() + ", " +
> value3.Tostring() +
> >> ")" ;
> >>
> >> HTH. If anything is unclear, please feel free to reply
> to the post.
> >>
> >> Kevin Yu
> >> =======
> >> "This posting is provided "AS IS" with no warranties,
> and confers no
> >> rights."
> >>
> >> --------------------
> >> | Content-Class: urn:content-classes:message
> >> | From: "Darwin S." <dar@xmission.com>
> >> | Sender: "Darwin S." <dar@xmission.com>
> >> | Subject: DataAdapter Parameter question
> >> | Date: Mon, 10 Nov 2003 13:14:38 -0800
> >> | Lines: 13
> >> | Message-ID: <09fc01c3a7cf$a67c2ad0$a001280a@phx.gbl>
> >> | MIME-Version: 1.0
> >> | Content-Type: text/plain;
> >> | charset="iso-8859-1"
> >> | Content-Transfer-Encoding: 7bit
> >> | X-Newsreader: Microsoft CDO for Windows 2000
> >> | X-MimeOLE: Produced By Microsoft MimeOLE
> V5.50.4910.0300
> >> | Thread-Index: AcOnz6Z5Ul/iKOnZSFSMRGFiWAys4w==
> >> | Newsgroups: microsoft.public.dotnet.framework.adonet
> >> | Path: cpmsftngxa06.phx.gbl
> >> | Xref: cpmsftngxa06.phx.gbl
> >microsoft.public.dotnet.framework.adonet:65963
> >> | NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
> >> | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
> >> |
> >> | I am currently dynamically adding a number of
> parameters
> >> | to a DataAdapter for the SELECT's where clause. The
> >> | where clause would actually be better to using a
> set, as
> >> | in "SELECT * FROM TABLE WHERE id IN (...)". Can a
> >> | parameter be set up to contain a string of values so
> that
> >> | only one paramter would need to be defined for the
> >> | query. Like so: SELECT * FROM TABLE WHERE id IN
> (@Parm1)
> >> |
> >> | Or do I need to create a parameter for each value in
> the
> >> | set?
> >> |
> >> | TIA,
> >> | Darwin
> >> |
> >>
> >
> >
> >.
> >