Cursor within an IF statement

[puredesi5]

Hi,

I have a procedure that reads a tbl_A using cursor.
declare curs_A
select fld_A1, fld_A2,....

fld_A1 values can be A1, B1, B1, C1....
I would like to create another cursor (curs_B) for only certain records for fld_A1 when it meets some criteria.

For example
If fld_A1 = 'B1'
declare curs_B
select fld_B1 from tbl_B
where fld_B1 = fld_A1

It gives me error when it enters IF statement second time saying curs_B exists.

Is there anyway to separate declare cursor from select.
Can I put decalre outside of IF and select statement within IF statement?

 

[bperry]

From your description, I am thinking that the second time around, when it hits the declare cursor statement, it can't re-declare the cursor because it already exists.

So you can't re-declare the cursor. Either adjust the code a little bit so that you are not re-declaring. Or perhaps provide a flag field so that you can tell that the cursor has in fact already been declared, so don't bother doing it again.

Unless you want to keep declaring it. In that case, you need to close/deallocate the existing cursor before attempting to create it again.

There is a function called CURSOR_STATUS. Check BOL to see if that can be used to determine if a cursor already exists or not.

bperry

 

[puredesi5]

bperry, thanks fo replying.

It appears that declare and select (for the cursor) go together as one statement? I don't think I can do something like this

If flag = 'N' -- check curs declared?
begin
declare curs_B for -- declare cursor
select flag = 'Y' -- set the flag
end
select * from tbl_B -- select for cursor

How would it know that second select is for cursor?

 

[puredesi5]

BTW, I am using SQL version 6.5.

 

[bperry]

>>How would it know that second select is for cursor?

The 'Select' used when declaring the cursor is used in contructing the cursor. Then you 'Open' it to actually gather the data. But then you don't do more Selects on that cursor to read the rows: what you do is start to 'fetch' the cursor.

I may not be following you here; if not, my apologies.

bperry

 

[puredesi5]

What I am trying to do is read data from curs_A and put data on curs_B when it meets some criteria, not everything read from curs_B. Then fetch data from curs_B.
I want to put on curs_B only data that has 'A' in fld_A

fetch curs_A into fld_A, fld_B,....

if fld_A = 'A'
begin
declare curs_B for
select * from tbl_b.....
end
/* Above code gives me message that curs_B is declared already. Which is right */
.
.

open curs_B
fetch curs_B into ....

It appears that declare needs to be executed once and select as many times as there are 'A' in fld_A.

I am not sure how to do this.




declare curs_B

 

[bperry]

Well, I think I see now, and I don't think that that can be done. The Select gathers up all the data at once, and you don't add iteratively to it as you parse the first cursor.
That's my thought, anyway, unless someone else sees something I don't.

I'd like to add that this is a rather strange situation. The accepted wisdom is that we are generally reluctant to create a cursor unless forced into it (by some circumstance that typical set-based processing can't resolve.) And I'm sure the point applies even more to 'creating a cursor within a cursor.'

We can't be sure of all the things you are facing there, but I'd be willing to bet there's a better, (and probably) set-based approach to your problem. In this situation you are definitely not using SQL Server the way it was meant to be used.

So there you go, but I'm often wrong!

bperry

 

[puredesi5]

I didn't think I could do something like that, but it was worth asking. Maybe somebody will come up with an idea. Thanks anyway. This forum is great.

 

[puredesi5]

I did it!!!

I wrote a separate and called the new procedure from the if statement and called procedure did everything I wanted to do.

Accomplised the goal.