|
|
Posted by Erland Sommarskog on 11/18/05 23:31
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_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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|