Re: CASE problem

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@xxxxxxxxxxxxxx) 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



.

Relevant Pages

  • Re: Order of execution in logical expressions
    ... expressions. ... But this has nothing to do with execution order. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server float data type
    ... A fundamental data type that holds double-precision floating-point ... And form SQL Server when trying to commit 4.94E-320: ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: CASE problem
    ... safe way to avoid evaluation is to use a CASE expression. ... expressions - always return the one and same data type. ... according to the data-type precedence rules in SQL Server. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Converting Int value to datetime
    ... from this two field in another table in date time format. ... has date time data type column respectively. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: ADODB Command (Stored Procedure)
    ... I also changed the stored procedure name to "procRecalculate". ... And the data type for TotalCost is? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
Loading