stored procedure with array of parameters

[Rick]

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

[Hugo Kornelis]

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)

[Erland Sommarskog]

Rick (ri**@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_preparedocumet @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_removedocument @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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Rick]

This helped get me going, Thanks.

[Rick]

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