|
|
Posted by Erland Sommarskog on 11/28/05 10:34
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.
--
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
Navigation:
[Reply to this message]
|