Posted by Erland Sommarskog on 11/29/05 00:41

Jeff Gilbert (blackhole@gilbertnet.net) writes:
> Ok, I was able to resolve it, but the solution doesn't make sense to me:
>
> I basically added to each branch a SUBSTRING(Number, 4, 6) call:
>
> CASE @documentType
> WHEN 1 THEN 'ST-' + dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,
> 4, 6) AS int)), 0) + 1, 6)
> WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6)
> AS int)), 0) + 1, 8)
> ELSE dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,4,6) AS int)),
> 0) + 1, 5)
> END
>
> This should fail, but it doesn't; it actually solves the problem. Can
> anyone shed some light on this behavior?

I don't see why this would fail, but I realise that I spoke too soon
in my explanation this morning.

Playing around a little, it appears that the problem lies in the MAX.
You ask for MAX(CAST Number AS int), and appearently SQL Server always
compute this expression; probably because it first computes the aggregate,
before it deals with the CASE. If you instead have CAST(MAX(Number) AS int),
you don't get the error message.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]