stored procedure with array of parameters

I have a table on the database with columns like the following:
Name          Date              Data
Joe             11/5/05          data1
Joe             11/6/05          data2
Bob             11/5/05         data3
Bob              11/8/05        data4


I want to retrieve all data from an array or list I pass in that
contains
one row for each name and a date, like below.
Name   Date
Joe      11/6/05
Bob      11/7/05

I want to retrieve all rows from the first table where Name is Joe and
Date > 11/6/05 or where Name is Bob and Date is > 11/7/05.  There could
be an unlimited number of name/date combinations.

Can anyone suggest a way to write a stored procedure to handle this
query.
Thanks,
Rick

On 17 Nov 2005 09:44:28 -0800, Rick wrote:

>I have a table on the database with columns like the following:
>Name          Date              Data
>Joe             11/5/05          data1
>Joe             11/6/05          data2
>Bob             11/5/05         data3
>Bob              11/8/05        data4
>
>
>I want to retrieve all data from an array or list I pass in that
>contains
>one row for each name and a date, like below.
>Name   Date
>Joe      11/6/05
>Bob      11/7/05
>
>I want to retrieve all rows from the first table where Name is Joe and
>Date > 11/6/05 or where Name is Bob and Date is > 11/7/05.  There could
>be an unlimited number of name/date combinations.
>
>Can anyone suggest a way to write a stored procedure to handle this
>query.
>Thanks,
>Rick

Hi Rick,

If the table that you have is called Table1 and the selections are in
Table2, then use the following query:

SELECT     t.Name, t.Date, t.Data
FROM       Table1 AS t
INNER JOIN Table2 AS s
ON   t.Name = s.Name
AND  t.Date > s.Date

And before you ask "yeah, but how do I _get_ my array or list into
Table2", click on the link below to read what Erland has to say about
it:

http://www.sommarskog.se/arrays-in-sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Rick (rick@abasoftware.com)  writes:
> I have a table on the database with columns like the following:
> Name          Date              Data
> Joe             11/5/05          data1
> Joe             11/6/05          data2
> Bob             11/5/05         data3
> Bob              11/8/05        data4
>
>
> I want to retrieve all data from an array or list I pass in that
> contains
> one row for each name and a date, like below.
> Name   Date
> Joe      11/6/05
> Bob      11/7/05
>
> I want to retrieve all rows from the first table where Name is Joe and
> Date > 11/6/05 or where Name is Bob and Date is > 11/7/05.  There could
> be an unlimited number of name/date combinations.
>
> Can anyone suggest a way to write a stored procedure to handle this
> query.

@xml = '<Data Name="Joe" Date="2005-06-11"/>
<Data Name="Bob" Date="2005-07-11"/>
EXEC  sp_xml_preparedocume
t @doc OUTPUT, @xml
SELECT Data
FROM   tbl t
WHERE  EXISTS (SELECT *
FROM OPENXML(@doc, '/Data', 0)
WITH (name varchar(30),
date datetime) AS o
WHERE t.name = o.name
AND o.date > t.date)
EXEC  sp_xml_removedocumen
t @doc


I did not test this, so you may have to look up OPENXML in Books Online
to get all details right.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

This helped get me going, Thanks.

Tthe other suggestion works but I may play around with
this xml version, Thanks.
Rick