Stored procedure output cached in cursor?

Question

Stored procedure output cached in cursor?

Viewing 4 posts - 1 through 5 (of 5 total)

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

KubaB SSC-Addicted Points: 443 More actions · Answer 1

Hmm. Well, haven't worked it out so if anyone has an idea I'd still be interested to know...

However I've gotten around the problem by sticking the SP Code into a multi statement table function and that seems to do the job.

Weird though!

WayneS SSC Guru Points: 95461 More actions · Answer 2

I see no reason why your variables in the c.u.r.s.o.r. are not being updated - would you mind posting the entire procedure for us to look at?

Also, if you can post the code for the [dbo].[Ascent_GetPostCodeDetails] procedure, as well as Table DDL, sample data via insert statements, and expected results based on the sample data (how to do all of this is in the first link in my signature), I'm sure that we can get rid of the c.u.r.s.o.r. that you're using.

FYI, a multi-statement table-valued function (TVF) is typically extremely slow.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KubaB SSC-Addicted Points: 443 More actions · Answer 3

Hi! Sorry I totally missed the etiquettes post.. Yeah testing the table function is totally killing the server! But at least it's working..

There's a few tables involved so it'll take a me a little while to put the test data together so when I have a bit of time later today... In the meantime though, I've fixed the SP but I don't understand why the fix works...

Going through the [dbo].[Ascent_GetPostCodeDetails] SP, I figured out that it is going wrong at a point where it is setting a variable using a select.

So changing the code from:

select@Area_ID= a.id

from_Area_Postcode as ap (nolock)

inner join _Area as a (nolock)

ON a.id = ap.area_id

wherea.iso_country_code= @ISO_Country_Code

ANDap.postcode_outcode= @Outcode

ANDap.postcode_incode= @Incode

to:

set @area_id=(

selecta.id

from_Area_Postcode as ap (nolock)

inner join _Area as a (nolock)

ON a.id = ap.area_id

wherea.iso_country_code= @ISO_Country_Code

ANDap.postcode_outcode= @Outcode

ANDap.postcode_incode= @Incode

)

fixes the problem. Is this weird? Is this something I should expect to happen?

Let me know if a complete sample will help I'll try and post it up later

Edit: Actually I'll post it up anyway because I'd be interested to see what better solution there is to a cursor..

KubaB SSC-Addicted Points: 443 More actions · Answer 4

OK. OK. So I got it.

If I print the area_id from the SP, it obviously caches the last result of the output.

The select statement returns nothing in it's first pass, and so doesn't update the area_id, and so the SP has the last area ID and so as far as it's concerned it's happy and therefore chucks back the same data.

The set statement returns null

So basically, lessons learned: SPs cache last output, and an important difference between select and set is that set is set to null when there are no results, and select doesn't do anything.