|
Home > Archive > Microsoft SQL Server forum > November 2005 > stored procedure with array of parameters
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Hugo Kornelis 2005-11-18, 8:24 pm |
| 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 2005-11-18, 8:24 pm |
| 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
|
|
|
|
|