Null returned in stored procedure look up

Question

Null returned in stored procedure look up

louise.prior

SSC Journeyman

Points: 84
More actions
August 3, 2009 at 3:45 am

#136842

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?

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

If the parameters should always return a value then I don't why you would get a NULL without an error. A deadlock or lock timeout should return an error not a NULL.

Without seeing the entire batch process it is a bit difficult to provide any concrete advice. One thing that I would suggest is to determine if you can hit that table once and populate a temporary table with the values needed for the rest of the process.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323547 More actions · Answer 2

tough call; i'm guessing that you really need to join the the data you are testing to the same two tables Rate_table

and Rate_price_table;

with 6 conditions in the WHERE statement, I'd think it might be possible/probable that there are items that cannot be found, especially oncerning data within the data ranges.

I know you said you were confident that the data was all within the allowed ranges, but i'm thinking thre must be a few exceptions that were not considered; there's no way any of the parameters are NULL? no chance at all?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

louise.prior SSC Journeyman Points: 84 More actions · Answer 3

Thanks for the replies guys.

I need to call the table each time to find out the particular rate that should apply to a policy. As I say, it might not be coded ideally, but it does work most of the time.

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.

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.

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 SSC-Dedicated Points: 39758 More actions · Answer 4

A bunch or replies here too

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130520

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.16"
E 12°55'05.25"