By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

stored procedure with array of parameters

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)
Nov 23 '05 #2

P: n/a
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
Nov 23 '05 #3

P: n/a
This helped get me going, Thanks.

Nov 23 '05 #4

P: n/a
Tthe other suggestion works but I may play around with
this xml version, Thanks.
Rick

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.