Query runs by itself, but fails in stored proc...

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

Transact-SQL (2008)

Query runs by itself, but fails in stored proc...

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 17:07:43

The following code runs fine when I run it by itself, but as part of a stored proc it generates a divide by zero error:

SELECT COUNT(*) AS 'Amount per item > $250,000'
FROM someTable 
WHERE 
	([count] > 0) AND 
	(([amount] / [count]) > 250000)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-17 : 17:12:03

CASE WHEN [count] = 0 THEN 0
ELSE [amount] / [count]
END

cvraghu
Posting Yak Master

187 Posts

Posted - 2012-02-17 : 17:23:33

SQL Server 'AND' operator does not do short circuit evaluation like c# &&, if that's what you tried to do.

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 17:49:59

quote:
Originally posted by russell

CASE WHEN [count] = 0 THEN 0
ELSE [amount] / [count]
END

Actually I thought the fix to this would be easy, but now the fix that should work is giving me problems as well.

My fix was simply:


SELECT COUNT(*) AS 'Amount per item > $250,000'
FROM 
  (SELECT [count], [amount] FROM someTable
   WHERE [count] > 0) t
WHERE 
  (([amount] / [count]) > 250000)

But, amazingly, this also gets a divide by zero error!

When I run the query for the virtual table and order by [count] I can see that the lowest value for [count] is 1!

How can this be generating a divide by zero error?

This gets even more weird. I created a test sample in a testing database, and all of the code works fine there. The real code is a little different from the example I'm using here, but nothing major, mostly just the columns and table names. I ran DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; thinking that might be an issue, but still for some reason run against the real data I keep getting this error, so I went a step further and I did the following (the real code this time):


SELECT 
	non_zero_claim_count, claim_amount_total_incurred, (claim_amount_total_incurred / non_zero_claim_count)
FROM 
	(SELECT non_zero_claim_count, claim_amount_total_incurred
	FROM std_Insured 
	WHERE (ISNULL(non_zero_claim_count, 0) > 0) AND (exclusion_count = 0) AND (transmission_id = 33)) i
WHERE 
	(claim_amount_total_incurred / non_zero_claim_count) > 250000

In this code, if I run this WITHOUT the where clause, then it runs fine, but if I include the WHERE clause I get a divide by zero error! How can this be since without the WHERE clause I'm still dividing every record????

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 17:51:15

quote:
Originally posted by cvraghu

SQL Server 'AND' operator does not do short circuit evaluation like c# &&, if that's what you tried to do.

Thanks

Yes, that was what I was trying to do, but even with another approach that should be working its still not working. See my prior post.

Also, I ran this test and it did work:


CREATE TABLE someTable
(
	[count] smallint NOT NULL,
	[amount] decimal(19,4) NOT NULL
);

INSERT INTO someTable
([count], [amount])
VALUES
(0,500000),
(1,500000),
(2,500000),
(3,500000),
(4,500000),
(5,500000),
(6,500000),
(7,500000),
(8,500000);

SELECT COUNT(*) AS 'Amount per item > $250,000'
FROM someTable 
WHERE 
	([count] > 0) AND 
	(([amount] / [count]) > 250000);

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 17:57:55

I finally got something to work, but it seems totally bizarre.

I tried using a CTE, like:


WITH Claims (non_zero_claim_count, claim_amount_total_incurred)
AS
(
	SELECT non_zero_claim_count, claim_amount_total_incurred
	FROM std_Insured 
	WHERE (ISNULL(non_zero_claim_count, 0) > 0) AND (exclusion_count = 0) AND (transmission_id = 33)
)
SELECT COUNT(*) AS 'Amount per claim > $250,000'
FROM Claims
WHERE 
	(claim_amount_total_incurred / non_zero_claim_count) > 250000

This still generates the divide by zero error.

Then I tried a temp table:




SELECT non_zero_claim_count, claim_amount_total_incurred
INTO #Claims
FROM std_Insured 
WHERE (ISNULL(non_zero_claim_count, 0) > 0) AND (exclusion_count = 0) AND (transmission_id = 33)

SELECT COUNT(*) AS 'Amount per claim > $250,000'
FROM #Claims
WHERE 
	(claim_amount_total_incurred / non_zero_claim_count) > 250000

DROP TABLE #Claims

This works.

Something in the order of operations is whack....

cvraghu
Posting Yak Master

187 Posts

Posted - 2012-02-17 : 18:12:17

Probably your count column has at least one null value.

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 18:19:05

quote:
Originally posted by cvraghu

Probably your count column has at least one null value.

I thought that too, but that's why I added ISNULL in the subquery. Also, I just ran the following test and it worked:


CREATE TABLE someTable
(
	[count] smallint NULL,
	[amount] decimal(19,4) NULL
);

INSERT INTO someTable
([count], [amount])
VALUES
(NULL, 500000),
(0,500000),
(1,500000),
(2,500000),
(3,500000),
(4,500000),
(5,500000),
(6,500000),
(7,500000),
(8,500000);

SELECT COUNT(*) AS 'Amount per item > $250,000'
FROM someTable 
WHERE 
  ([count] > 0) AND 
  (([amount] / [count]) > 250000);

So it seems that even with a NULL it should work...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-17 : 18:26:47

Depending on the plan the optimiser comes up with (which can and will change depending on row counts and a whole bunch of other things), that query may succeed or may fail with a divide by zero. Completely depends on the order chosen for the operations.

Russell gave a solution that will work in the second post in this thread.

--
Gail Shaw
SQL Server MVP

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-17 : 18:44:23

quote:
Originally posted by GilaMonster

Depending on the plan the optimiser comes up with (which can and will change depending on row counts and a whole bunch of other things), that query may succeed or may fail with a divide by zero. Completely depends on the order chosen for the operations.

Russell gave a solution that will work in the second post in this thread.

--
Gail Shaw
SQL Server MVP

It's definitely something to do with the query plan being chosen, which is why it was working when run by itself but then not working as part of the stored proc.

I would think, however, that using the CTE would have resolved it no matter what, or for that matter that even the use of the virtual table would have resolved it. The temp table seems to have solved it & perhaps I could get the same effect with a table variable.

Yeah, I could also use:

WHERE
CASE WHEN [count] = 0 THEN 0 ELSE [amount] / [count] END > 250000

It just seemed as though using a simple virtual table or CTE should have solved it. I thought that with a virtual table the inner query was done in totality first, then the result of that was passed to the outer part, but apparently that isn't the case, its like its executing the where clause on records that don't meet the conditions of the where clause in the subset, which is just not what I expected to happen...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-18 : 04:58:43

No, a subquery (and a CTE is just a named subquery) is not done in totality first, the optimiser has rewrite options for the subquery and outer query for better plans. The myth that subqueries are executed in totality first is a prevelent one, and probably why many people think they perform badly. I can easily write 2 queries, one using a subquery and one with a join and show they have exactly the same execution plans.

Table variable and temp table will work, because you are splitting the query into 2 parts (whereas a subquery or CTE is just part of a single query and executed as a single piece), however that's overkill and not necessary. The CASE expression is very likely the way to go here

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources