Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>