Posted by Jeff Gilbert on 11/28/05 21:32

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?

Jeff...

> Jeff Gilbert (blackhole@gilbertnet.net) writes:
>
>> Ok, so now that you have the information, here's the problem. It
>> seems that each result_expression (the expression after each THEN
>> clause) gets evaluated no matter which statement gets returned.
>> Although admittedly this explanation isn't consistent, it's the
>> closest I can come to understanding the problem. The symptom is that,
>> when there is at least one record saved in the Document table as a
>> PurchaseOrder sub-type (and so the Number field is "ST-000001"), each
>> subsequent call to the proc with @documentType = 1 results in:
>>
>> Server: Msg 245, Level 16, State 1, Procedure
>> GetNextInSequenceStockton,
>> Line 6
>> Syntax error converting the varchar value 'ST-000001' to a column of
>> data
>> type int.
> No, it's not that each THEN expression gets evaluated. In fact, the
> only safe way to avoid evaluation is to use a CASE expression. For
> instance, this is not safe:
>
> SELECT a/b FROM tbl WHERE b <> 0
>
> But this is:
>
> SELECT CASE WHEN b <> 0 THEN a/b END WHERE b <> 0
>
> The issue you are running into is that a CASE expression - like all
> other expressions - always return the one and same data type. If the
> different THEN expressions are of different data types, they will be
> converted according to the data-type precedence rules in SQL Server.
> And in this precedence order, varchar is low on the list.
>
> You should probably throw in a convert(varchar for the numeric
> branches in the CASE expressions.
>
> 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]