August 1, 2003 at 5:47 pm
Hi!
Before the whole SQL Server Central community cringes at the thought of having embedded cursors, can someone tell me if this is even supported?
Now, I will tell you why I feel I need them and am VERY open to other suggestions.
I have 1 master table: Master
I have 2 misc tables: TableA and TableB
The common link between the tables is claim#. TableA may contain 2 rows pertaining to a particular claim#, while TableB may contain 4 (or +/-) rows. I am being told that I must populate my MASTER table in this fashion: TableA/row1 data and TableB/row1 data in MASTER/row1. TableA/row2 data and TableB/row2 data in MASTER/row3, TableB/row3 in MASTER/row3, TableB/row4 in MASTER/row4. I only have 1 MASTER row per claim#.
The only way I can figure out how to solve my problem is to have a top level cursor sequentially reading a row for each claim# and then within TableA, developing a cursor to cull these rows and either perform an INSERT or UPDATE to the MASTER and then develop/reuse the cursor for TableB to loop through these rows for that claim# and then to return to the top and fetch a new claim#.
Am I missing something that one can do with T-SQL to solve this problem? Does this even make sense?
Any and all suggestions will be greatly appreciated!
Janet
August 4, 2003 at 8:00 am
This was removed by the editor as SPAM
August 5, 2003 at 2:58 am
if you really want to use embedded cursors, do it like that:
declare @a int
declare @b-2 int
declare cursor foo for
select a from master_table
open foo
fetch next from foo into a
while @@fetch_status = 0 begin
delcare cursor hugo for
select b from detail_table
where master_id = @a
open hugo
fetch next from hugo into b
while @@fetch_status = 0 begin
print @a
print @b-2
fetch next from hugo into b
end
close hugo
deallocate hugo
fetch next from foo into a
end
close foo
deallocate foo
you should be aware that a solution like that is not very optimal 😉 unfortunately you'll have to redeclare the inner cursor for every iteration of the outer one because unlike oracle mssql does know nothing about parametrized cursors ...
best regards,
chris.
August 5, 2003 at 6:19 am
Is there anything in the tables to identify the rows to enable a match. Without any row numbering even cursors may give you inconsistent results, remember sql server does not guarantee row order without an ORDER BY clause.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply