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

Oops, one other point: The value "ST-000001" is the INPUT value into the
branch, not the output value, as this is the second time the proc is being
run. "ST-000001" is the current MAX value in the column which is getting
selected. The output should be "ST-000002".

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]