Stored procedure output cached in cursor?

Question

Stored procedure output cached in cursor?

KubaB

SSC-Addicted

Points: 443
More actions
November 1, 2010 at 6:26 am

#230607

We have a stored procedure that runs when an address is updated. The SP has 4 output variables - area, region, subregion and office ID and takes the postcode and country code as input.
There are a number of records that need updating, and if I run the SP against one record I get back results as expected. SO I decided to stick the code into a cursor to do a batch update. The weird thing is that printing the results back only ever returns the values from the first record. Is this a weird SP with output variables in a cursor thing?
Here is my code:
declare
@RegionINTEGER,
@Sub_RegionINTEGER,
@Area_IDUNIQUEIDENTIFIER,
@Office_IDUNIQUEIDENTIFIER,
@postcode nvarchar(10),
@iso_country_code nvarchar(10),
@id UNIQUEIDENTIFIER
DECLARE db_cursor CURSOR FOR
Select id, postcode, iso_country_code
from dbo._Company nolock where reference in (
-- a bunch of references
)
order by newid() -- just to prove it does change the values returned
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id, @postcode, @iso_country_code
WHILE @@FETCH_STATUS = 0
BEGIN
set @postcode=rtrim(ltrim(@postcode))
exec [dbo].[Ascent_GetPostCodeDetails]
@postcode,
@iso_country_code,
@Area_ID OUT,
@Region out,
@Sub_Region OUT,
@Office_ID OUT
print @postcode + ', '+convert(nvarchar(10), @region) + ', '+convert(nvarchar(10),@sub_region)+', ' + convert(nvarchar(36), @Area_ID)
--WAITFOR DELAY '000:00:01' -- no change..
FETCH NEXT FROM db_cursor INTO @id, @postcode, @iso_country_code
END
CLOSE db_cursor
DEALLOCATE db_cursor
So the code above prints out the different post codes but with the same values returned for each one. If I run them seperately, it gives me correct values. What am I missing? Is it possible the SP is caching the first result? If so, how do I make it not do that? Not seen that before...
Would appreciate any suggestions! Tried it in a while loop too - no change. Is there a better way than using a cursor?

Viewing 5 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.