Null returned in stored procedure look up

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 2005 Forums

Transact-SQL (2005)

Null returned in stored procedure look up

Author

Topic

louiser
Starting Member

3 Posts

Posted - 2009-08-03 : 05:49:19

I have a stored procedure which is running repeatedly as part of an intensive batch process (takes several hours to run) which includes the following query to look up a rate value:

set @Rate = (
select PR_RATE
from Rate_table
join Rate_price_table
on PR_RATE_ID = RC_RATE_ID
where RC_PRODUCT = @Product and RC_BENEFIT = @Benefit and
RC_TYPE=@RCType and
@RCLookUp between RC_START and RC_END and
@PRStartDate between PR_FROM_DATE and PR_TO_DATE and
@PRPaymentDate between PR_START_DATE and PR_END_DATE)

The statement is run approximately 500 times during the batch with different parameters. It might not be ideally formatted, but it works fine almost all the time.

However, sporadically during the batch run, it returns null values. It doesn't do so for any of the parameter values when run individually, nor does it fail consistently for the same parameters during repeated runs of the batch. So I'm confident that it's not a problem with missing values on the tables.

It feels like it must be some sort of performance problem, but there is no sign of a lockout error being returned. When we've added more debug to the procedure, it has (sometimes) encountered an increased frequency of the Null returns... I suspect that the debug has added load, hindered performance & exacerbated whatever the problem is.

I'm a bit woolly on the internal workings of SQL server... can anyone shed any light on the problem?

asgast
Posting Yak Master

149 Posts

Posted - 2009-08-03 : 11:26:24

@rate would be null if your subquery returned more than 1 result, but this would trigger an error

its rather difficult to say why sp fails if you show only a part of it

I would suggest to look into variables you pass to this query if one of them is null then @result would be also null
try logging all the variables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 11:28:40

No, if the subselect has more than one record, the subselect will fail.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 11:31:33

And this will give the variable the last record value

DECLARE	@Peso INT

SELECT	TOP(1000)
	@Peso = Number
FROM	master..spt_values
WHERE	Type = 'P'

SELECT	@Peso

N 56°04'39.26"
E 12°55'05.63"

asgast
Posting Yak Master

149 Posts

Posted - 2009-08-03 : 11:34:57

well yes
@rate would be null if your subquery returned more than 1 result, but this would trigger an error
I think I have said the same here :)
I would not recommend using a subselect here

but its possible that this query is failing because @product is null or any other variable is null

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-03 : 12:35:05

quote:
Originally posted by asgast

but its possible that this query is failing because @product is null or any other variable is null

That is a possibility.

louiser
Starting Member

3 Posts

Posted - 2009-08-03 : 13:05:38

Hi guys,
Thanks for all the suggestions.
Unfortunately we know from debug that all the @ variables are populated, but that the select fails to populate @Rate when it runs sometimes.
i.e. say on the 5th execution within one batch run, it works fine & the rate is set.
Next time we run the batch, on the 5th execution of the select, with the same variables as were provided on the 5th execution last time, the rate is returned null.

It's like there's something temporarily preventing the @Rate from being set by the select, or silently preventing the query from having access to the tables.

We seem to get a run of null returns within the batch, e.g. in one run we'll get nulls for 20th-23rd execution then again for 69-98 then the rest will be fine.
Next time the above occurances will execute fine but the 50th-57th execution of the select will fail (with the parameters input in the same sequence).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-03 : 13:52:53

I'm not sure how you are populating your variables but another option is that there no rows meeting that criteria, and thus you get a null value assigned to @Rate.

Do you have an temporal gaps that could cause this?

louiser
Starting Member

3 Posts

Posted - 2009-08-04 : 04:16:29

Can you expand on what you mean by 'temporal gaps'?

I know from debug that the parameters are never null.
The rate returned is null, but not consistently between different runs of the batch. The null values tend to occur in a block, but the next time the batch runs (with the same parameters executing the code in the same order), a different set of parameters will return null values.

To make matters worse, the problem only occurs in production. I've backed up the database & restored it to a test server & it runs without any problems. - All the same tables, populated with exactly the same values, running exactly the same code.

It can't be anything to do with data on the tables being wrong & the code is fine, there's something going on on the server that prevents the @rate value from being set.

All I can think of is that the table is being silently locked, or the server 'memory' is dropping results because it's busy e.g. sorting out log files periodically.... but surely SQL server is coded so that that never happens.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 04:34:53

Even if the parameters are never NULL, they form a query which filters by the parameters.
The result of the query may not match any record ans thus you get an empty resultset back and the variable is set to NULL.

N 56°04'39.26"
E 12°55'05.63"

asgast
Posting Yak Master

149 Posts

Posted - 2009-08-04 : 05:05:04

as louiser has explained that when query is run later with these parameters everything is ok.

is it possible that Rate_table and Rate_price_table are changed between the batch runs? This would make perfect possibilities for temporal gaps to arise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources