Need help with stored procedure with cursor

Question

Post reply

Need help with stored procedure with cursor

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 1

Thanks also for this tidbit - now that I have the process working, I can play around with options and refinements!

Really appreciate all your help.

Kenena

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

It should actually be

WHILE @@FETCH_STATUS = 0

From BOL

@@FETCH_STATUS

Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Return value Description

0 FETCH statement was successful.

-1 FETCH statement failed or the row was beyond the result set.

-2 Row fetched is missing.

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 3

Hello Matt -

The gridkey is a sequence key required by the application, and it is obtained from a grid sequencing table. Many keys in the application come from such tables, and I will be using this type of SP to insert outside data on a regular basis. Some of these inserts will involve several tables that each have to pull the next sequence from their specific sequence table. As the developers had already written the SP to get the next sequence and the samples I review used cursors, I thought that was what I had to do. Being inexperienced, I've had a learning curve trying to figure this out and have frequently read the "don't use cursors" info. I'm very open to exploring other formats, especially now that I have something that will work.

Thanks for your help and all suggestions are welcome!

Kenena

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 4

Thanks mrpolecat -

I had read that in BOL, but I had interpreted the -1 to mean there were no more rows left so as long as <> -1 it would keep fetching.

I guess 0 is saying I found something and I fetched it and I'll keep doing it until I don't find anything

Thanks for the clarification.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

well - it's not murderously bad with only 60 records, but 60,000 will cause your DB some nasty fits, and 6 million records...well you could go get lunch and then some.

Perhaps if you can expose what your stored proc is actually doing - we can combine the two processes into something setbased that won't make your server go into conniptions.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 6

Hello Matt -

It may be proprietary, but bottom line, it goes to the sequence table ID listed in the SP, gets the next value, adds one to the sequence table and continues till done. In the past, I have pulled the number of sequences I need from the table and inserted them into the table I am going to import, but going forward, these SPs will be overnight processes. If the whole thing can be done through a DTS process, that's even better.