Need some big time help on this Cursor syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need some big time help on this Cursor syntax

Author

Topic

mcioffi
Starting Member

9 Posts

Posted - 2002-11-04 : 11:58:47

I have been doing so much oracle the last 2 years I cannot figure out the syntax for this. It keeps grabbing the second row that the select produces. It inserts that row twice. What am I doing wrong. The insert is not pretty, I know, I will fix that once I get the majority of the cursor working properly.

****************************************************************

declare
@acct_sec_id varchar(20),
@acct_id varchar(20),
@sec_id decimal(10,0),
@UID decimal(10,0)

declare ins_cur cursor
for
select p.acct_cd+'_'+convert(char(10),s.sec_id), p.acct_cd,s.sec_id
from cs_position p, csm_Security s
where p.sec_id = s.sec_id
and s.sec_typ_cd = 'CURR'
open ins_cur
fetch ins_cur into @acct_sec_id, @acct_id, @sec_id
--fetch next from ins_cur into @acct_sec_id, @acct_id, @sec_id
WHILE @@FETCH_STATUS =0
BEGIN
execute @uid = f_get_sequence 1
--fetch next from ins_cur into @acct_sec_id, @acct_id, @sec_id
fetch ins_cur into @acct_sec_id, @acct_id, @sec_id
WHILE @@FETCH_STATUS =0
BEGIN
--execute @uid = f_get_sequence 1
fetch ins_cur into @acct_sec_id, @acct_id, @sec_id
--fetch next from ins_cur into @acct_sec_id, @acct_id, @sec_id
insert into MJC_tax_lot
select @uid,@acct_sec_id,NULL,@ACCT_ID,
@SEC_ID,'L',getdate(),getdate(),1000000000,NULL,
NULL,NULL,1000000000,0,1,1,NULL
from cs_position p, csm_Security s
where p.sec_id = s.sec_id and s.sec_typ_cd = 'CURR'
END
END
close ins_cur
deallocate ins_cur

*********************************************************************

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-04 : 12:05:29

why are you running
fetch ins_cur into @acct_sec_id, @acct_id, @sec_id
4 times throughout your entire script here? Is there any reason why you have 2 while loops? Trimming out the extra fetches and the extra while loop

--declarations go here... including cursor
open ins_cur
fetch ins_cur into @acct_sec_id, @acct_id, @sec_id
WHILE @@FETCH_STATUS =0
BEGIN
execute @uid = f_get_sequence 1
insert into MJC_tax_lot
select @uid,@acct_sec_id,NULL,@ACCT_ID,
@SEC_ID,'L',getdate(),getdate(),1000000000,NULL,
NULL,NULL,1000000000,0,1,1,NULL
from cs_position p, csm_Security s
where p.sec_id = s.sec_id and s.sec_typ_cd = 'CURR'
fetch next from ins_cur into @acct_sec_id, @acct_id, @sec_id
END
--close cursor and so on

Try that. Next step would be to figure out why your using a curosr and switch ya over to setbased

edit note for you. Should include the column listing in your insert statement for your table. like:
insert into MJC_tax_lot (col1,col2.....)
select .....

Just good practice more then anything. I've had too many things break around here because a table was altered and procs with inserts didn't have all the columns listed

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 11/04/2002 12:08:13

mcioffi
Starting Member

9 Posts

Posted - 2002-11-04 : 12:18:25

M.E., Thanks that got me closer. The problem I have is that the select statement returns 2 rows, will be more later, I'm inserting each row twice. Why is that happening?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-04 : 12:27:56

My only guess for that was you were running 2 while statements and it was running the insert twice. Other then that I can't see any reason (using the code I provided).

It may be a data error as well with your where statement

select @uid,@acct_sec_id,NULL,@ACCT_ID,
@SEC_ID,'L',getdate(),getdate(),1000000000,NULL,
NULL,NULL,1000000000,0,1,1,NULL
from cs_position p, csm_Security s
where p.sec_id = s.sec_id and s.sec_typ_cd = 'CURR'

Clean it up and switch to joins

select blah
from cs_position p inner join csm_security s on p.sec_id = s.sec_id
where s.sec_typ_cd = 'Curr'

Is it possible that you are getting 2 records back from this join clause? depending on your data, it may need to be

where s.sec_typ_cd = 'Curr' and s.sec_ID = @sec_id

My other question is why is this from clause even here? In youre select statement I don't see one reference to p. or s. Could you give me a little more dtail on what your select statement is doing?

-----------------------
SQL isn't just a hobby, It's an addiction

mcioffi
Starting Member

9 Posts

Posted - 2002-11-04 : 12:34:01

BINGO!!!!!!!

It was the insert statement. I cut and pasted the insert, forgetting to remove the from and where clause.

insert into MJC_tax_lot
select @uid,@acct_sec_id,NULL,@ACCT_ID,
@SEC_ID,'L',getdate(),getdate(),1000000000,NULL,
NULL,NULL,1000000000,0,1,1,NULL
from cs_position p, csm_Security s
where p.sec_id = s.sec_id and s.sec_typ_cd = 'CURR'

Should be

insert into MJC_tax_lot
select @uid,@acct_sec_id,NULL,@ACCT_ID,
@SEC_ID,'L',getdate(),getdate(),1000000000,NULL,
NULL,NULL,1000000000,0,1,1,NULL

Thanks for the help M.E., you have no idea how much I apperciate it. It has been driving me crazy all weekend.

Next step is to clean up the insert itself. I was given it by a not so technical person.

Thanks again,
Mcioffi

Edited by - mcioffi on 11/04/2002 12:34:56

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-04 : 12:44:36

What does f_get_sequence do? Maybe you can get rid of the cursor all together and move towards a set based approach.

Jay White
{0}

mcioffi
Starting Member

9 Posts

Posted - 2002-11-04 : 12:48:40

page47, that is a sequence generator that is used through out the database. It generates a unique number, I'm using it to make sure the row inserted has a unique id. When you say a set based approach, what do you mean?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-04 : 12:49:15

I was wondering when an anticursor / pro-setbased person would say something in here

If you can give us more info about what all this does mcioffi, we should be able to come up with a good setbased approach

edit/ Setbased would be removing the loop. SQL is not design for an iterative process... It is much faster and more effiecient at setbased functions... entire set of data at once. Could your stored proc be converted into a user defined function?
-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 11/04/2002 12:51:36

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-04 : 13:01:55

quote:
SQL is not design for an iterative process... It is much faster and more effiecient at setbased functions... entire set of data at once.

Think of a needle in a haystack, or a bowl of sugar...a cursor only works on one straw or one grain of sugar at a time. You wouldn't want to use tweezers to look for the needle, or to drop single grains of sugar in your coffee. You'd use a magnet, or a spoon. SQL is the magnet/spoon.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-04 : 13:04:17

Hmm, ellegantly put rob. Heh, I'm borrowing that line next time I get asked.

-----------------------
SQL isn't just a hobby, It's an addiction

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-04 : 13:11:09

Set based Example ... replace your entire cursor and multiple loops with ...


insert into MJC_tax_lot
select
    0, -- We need to figure out how to deal with your seq number
    p.acct_cd+'_'+convert(char(10),s.sec_id),
    NULL,
    p.acct_cd,
    s.sec_id,
    'L',
    getdate(),
    getdate(),
    1000000000,
    NULL, 
    NULL,
    NULL,
    1000000000,
    0,
    1,
    1,
    NULL 
from cs_position p, csm_Security s 
where p.sec_id = s.sec_id and s.sec_typ_cd = 'CURR'

quote:
that is a sequence generator that is used through out the database

Can you post the code or give us a better understanding of exactly what it does? Is the returned number Globally unique or unique to the database or unique to just this table?

M.E. is right. SQL Server is a loosely termed Relational Database Management System. RDBMS' are built using set theory and predicate logic as a foundation for managing data. Therefore, it is likely that, when accessing said data, using set based methods will outperform iterative methods ... said simpler, you cursor is looping through your dataset looking at one row at a time and making decisions and taking actions. You process will be much faster if you make your decisions and take your actions on the set of data as a whole rather than one row at a time ....

There are many set based ways to deal with your Sequence number. Just give us a better understanding of your business rules and we can probably help you out.

Jay White
{0}

mcioffi
Starting Member

9 Posts

Posted - 2002-11-04 : 14:57:37

Thanks folks. I will get some more info out in a couple of hours, I'm in the middle of something else right now and don't have the time. But count on it in the next 2-3 hours. I would love to see another approach to the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources