Author: Russell, Stephen
Posted: 2002-11-08 at 10:01:37
Or you can make a SP on the server that gets a param string. Examine that
sting and parse through the ,'s execute that statement and return the
results.
Stephen Russell
Developer - Clinical Research Shared Resource
St. Jude Children's Research Hospital
Mail Stop 504
332 North Lauderdale St.
Memphis, TN 38105
(901) 495-3807 - Office
(901) 246-0159 - Cell
-----Original Message-----
From: Chuck Urwiler [mailto:chuck@eps-software.com]
Sent: Friday, November 08, 2002 9:35 AM
To: Multiple recipients of ProFox
Subject: RE: Remote View Inlist Parameterized where clause
Dan,
Unfortunately, I'm pretty sure you can't do what you're trying, since
SQL Server doesn't have an allowable syntax for this. In other words,
what SQL Server is getting is the following statement:
Select * from table where field in ( @p1 )
And there's no way for SQL Server to "expand" what you put into @p1,
which is the SQL Server side of the ?vp_list you're specifying. All this
will accept is a single value, no comma-delimited list acceptable here.
Alternatives: how many distinct values are you looking for? Could you do
this:
Select * from table where field = ?vp_p1 or field = ?vp_p2
Or, better yet, can you do this with data from the server? For example,
imagine that this query gets you the list (from the server) that you're
looking for:
Select field from othertable where somecondition = somevalue
(returns cursor with 'A','B','C')
Then you could send this:
Select * from table where field in (select field from other table where
somecondition = somevalue)
Instead of trying to parameterize the statement.
-Chuck Urwiler, MCT, MCSD, MCDBA
> -----Original Message-----
> From: Dan Neuman [mailto:dneuman@pclv.com]
> Sent: Thursday, November 07, 2002 7:52 PM
> To: Multiple recipients of ProFox
> Subject: Remote View Inlist Parameterized where clause
>
>
> I have a question for the SQL Server remote view gurus.
>
> I need to create a remote view to a SQL database, and in the
> where clause I
> would like to provide a list of values. And if the field
> matches one of
> those values, then return the record.
>
> Is there a way to do that?
>
> Something like:
> Local vp_List
> vp_List = ["A","B","C"]
> Use RemoteViewName
>
>
> Remote View SQL Statement:
> Select Table.* from dbo.Tablename where
> inlist(Tablename.Field, ?vp_List)
>
>
>