DataAdapter Parameter question

  • Thread starter Thread starter Darwin S.
  • Start date Start date
D

Darwin S.

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
 
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." <[email protected]>
| Sender: "Darwin S." <[email protected]>
| Subject: DataAdapter Parameter question
| Date: Mon, 10 Nov 2003 13:14:38 -0800
| Lines: 13
| Message-ID: <[email protected]>
| 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
|
 
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.
__________________________________
 
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.
__________________________________
 
Hi Darwin,

For more information, you can check the SQL Book Online.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| Content-Class: urn:content-classes:message
| From: "Darwin S." <[email protected]>
| Sender: "Darwin S." <[email protected]>
| References: <[email protected]>
<1sx#[email protected]>
<[email protected]>
| Subject: Re: DataAdapter Parameter question
| Date: Tue, 11 Nov 2003 09:54:10 -0800
| Lines: 110
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOofM95X+/tnLHoSHepQ5zHMwO0uw==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:66032
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| 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.
| >__________________________________
| >
| message
| >| >> 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." <[email protected]>
| >> | Sender: "Darwin S." <[email protected]>
| >> | Subject: DataAdapter Parameter question
| >> | Date: Mon, 10 Nov 2003 13:14:38 -0800
| >> | Lines: 13
| >> | Message-ID: <[email protected]>
| >> | 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
| >> |
| >>
| >
| >
| >.
| >
|
 
This may be totally out of left field but you could do it by passing in the
list as xml then using OPENXML you could construct a table with the values
therein from there you would simple look for values in the new table as your
criteria. How effective this would be would be a function of the number of
values you which to check against, but it does offer a method.

Hope that helps

Keith
 
Back
Top