Reply to Re: CASE problem

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация