CASE problem

Hello all. I'd appreciate some help with this one:

First the DDL:

CREATE TABLE [Document] (
[IDDocument] & #91;uniqueidentifier
] NOT NULL ,
& #91;IDParentDocument
] & #91;uniqueidentifier
] NULL ,
[IDDocumentType] & #91;uniqueidentifier
] NOT NULL ,
[Number] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NOT NUL
L ,
[CreationDT] [datetime] NOT NULL CONSTRAINT & #91;DF_Document_Crea
tio
nDate] DEFAULT
(getdate()),
CONSTRAINT [PK_Document] PRIMARY KEY  CLUSTERED
(
[IDDocument]
)  ON [PRIMARY] ,
CONSTRAINT [IX_Document] UNIQUE  NONCLUSTERED
(
[Number],
[IDDocumentType]
)  ON [PRIMARY] ,
CONSTRAINT & #91;FK_Document_Docu
ment] FOREIGN KEY
(
& #91;IDParentDocument
]
) REFERENCES [Document] (
[IDDocument]
),
CONSTRAINT & #91;FK_Document_Docu
mentType] FOREIGN KEY
(
[IDDocumentType]
) REFERENCES [DocumentType] (
[IDDocumentType]
)
) ON [PRIMARY]

CREATE TABLE [DocumentType] (
[IDDocumentType]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [
 DF_DocumentType_IDDo
cumentType]
DEFAULT (newid()),
[Name] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NOT NULL 
,
CONSTRAINT & #91;PK_DocumentType]
 PRIMARY KEY  CLUSTERED
(
[IDDocumentType]
)  ON [PRIMARY]
) ON [PRIMARY]


Next, information:

"Document" is the root type in an inheritance heirarchy which includes sub-t
ypes
such as "Purchase Order", "Requisition", "Work Order", and so on. Each docum
ent
type has it's own numbering scheme for it's identifying number (the PO numbe
r,
Req number, etc). In this case, PONumbers have a 3 digit identifier that's
static, then a 6 digits incrementing number; Work Orders have an 8 digit
incrementing number; all other documents have a 5 digit incrementing number.
I've written a stored procedure that returns the next number in the sequence
using a variant on the SELECT MAX() method:


CREATE    PROCEDURE  GetNextInSequenceSto
ckton
@documentType int
AS

SELECT
CASE @documentType
WHEN 1 THEN 'ST-' + dbo. PadNumber(COALESCE(M
 AX(CAST(SUBSTRING(Nu
mber, 4,
6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo. PadNumber(COALESCE(M
AX(CAST(Number AS int)), 0) + 1, 8)
ELSE dbo. PadNumber(COALESCE(M
AX(CAST(Number AS int)), 0) + 1, 5)
END
FROM
Document
WHERE
IDDocumentType =
CASE @documentType
WHEN 0 THEN 'E98E424B-7DFB-46EB-B610-EC5AB6FC69C1' --Requisition
WHEN 1 THEN '89CCFA98-36EC-4B9A-A2EF-4A86189CF87F' --Purchase Order
WHEN 2 THEN '42DA87E6-6F28-4D2D-9912-BBB1DB8F25C1' --Receiver
WHEN 3 THEN '5D942DE7-84FA-470C-9F8A-41B9370A2895' --Work Order
WHEN 4 THEN 'EBFA6AB8-6826-4863-AA40-2B6C042362E1' --Stock Issue Ticket
END

This stored procedure calls into the PadNumber UDF which takes an int and
returns a string representation of the number padded with the designated
number of zeros:

CREATE  FUNCTION PadNumber
(@numberToPad int,
@length int)
RETURNS varchar(50)

AS

BEGIN
RETURN  (REPLACE(STR(@number
 ToPad,@length),SPACE
(1),'0'))
END

So, the final result should look like this:

PurchaseOrder: ST-000001
WorkOrder: 00000001
All others: 00001

The Problem:

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 evaluate
d
no matter which statement gets returned. Although admittedly this explanatio
n
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  GetNextInSequenceSto
ckton,
Line 6
Syntax error converting the varchar value 'ST-000001' to a column of data
type int.

Here's the kicker: if I remove the "WHEN 3"... and "ELSE" clauses from the
SELECT statement the proc executes and returns properly; it's only when ther
e's
another clause besides "WHEN 1" in the select statement that the proc fails.
My assumption is that "WHEN 3" and "ELSE" are getting evaluated and executed
in memory or something (and failing as those clauses don't test for the pref
ix)
as removing them from the clause removes the problem. However, calls with
@documentType != 1 work fine everytime. I don't understand it. It seems that
this proc should work. What am I missing?

Thanks in advance for any help you can give.

Jeff...

Hello Erland,

Well, I'm not sure I understand. I believe each branch evaluates to varchar(
50)
as the result of the PadNumber UDF is varchar(50) and PadNumber() is the
final call in each branch. Any other thoughts?

Jeff...

> Jeff Gilbert  (blackhole@gilbertne
t.net) writes:
> 
> 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/pr...ownloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

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@gilbertne
t.net) writes:
> 
> 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/pr...ownloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

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(M
 AX(CAST(SUBSTRING(Nu
mber, 4,
6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo. PadNumber(COALESCE(M
 AX(CAST(SUBSTRING(Nu
mber, 4, 6) AS int))
,
0) + 1, 8)
ELSE dbo. PadNumber(COALESCE(M
 AX(CAST(SUBSTRING(Nu
mber,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@gilbertne
t.net) writes:
> 
> 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/pr...ownloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Jeff Gilbert  (blackhole@gilbertne
t.net)  writes:
> 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(M
 AX(CAST(SUBSTRING(Nu
mber,
> 4, 6) AS int)), 0) + 1, 6)
>      WHEN 3 THEN dbo. PadNumber(COALESCE(M
 AX(CAST(SUBSTRING(Nu
mber, 4, 6)
> AS int)), 0) + 1, 8)
>      ELSE dbo. PadNumber(COALESCE(M
 AX(CAST(SUBSTRING(Nu
mber,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?

I don't see why this would fail, but I realise that I spoke too soon
in my explanation this morning.

Playing around a little, it appears that the problem lies in the MAX.
You ask for MAX(CAST Number AS int), and appearently SQL Server always
compute this expression; probably because it first computes the aggregate,
before it deals with the CASE. If you instead have CAST(MAX(Number) AS int),
you don't get the error message.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I think the key point you made is, "...appearently SQL Server  always
> compute this expression".  The problem is that the "Number" column can
contain different formats for the various document sub-types. The weird thin
g
is that the predicate should effectively prevent the MAX() function from
evaluating on anything but the set of values that the THEN clause should
be operating on. But, apparently it doesn't. The reason why inserting the
SUBSTRING(Number, 4, 6) shouldn't work is because for a 5 digit number (anyt
hing
but a PO or Work Order) the SUBSTRING() function should return null or empty
string (not sure of the behavior). I really don't get it. I think I have
the semantics as to how the script should work, but apparently the Sql is
getting interpreted incorrectly? Is this a bug?

Jeff...

> Jeff Gilbert  (blackhole@gilbertne
t.net) writes:
> 
> I don't see why this would fail, but I realise that I spoke too soon
> in my explanation this morning.
>
> Playing around a little, it appears that the problem lies in the MAX.
> You ask for MAX(CAST Number AS int), and appearently SQL Server always
> compute this expression; probably because it first computes the
> aggregate,
> before it deals with the CASE. If you instead have CAST(MAX(Number) AS
> int),
> you don't get the error message.
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...ownloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Jeff Gilbert  (blackhole@gilbertne
t.net)  writes:
> I think the key point you made is, "...appearently SQL Server always
> compute this expression".  The problem is that the "Number" column can
> contain different formats for the various document sub-types. The weird
> thing is that the predicate should effectively prevent the MAX()
> function from evaluating on anything but the set of values that the THEN
> clause should be operating on. But, apparently it doesn't. The reason
> why inserting the SUBSTRING(Number, 4, 6) shouldn't work is because for
> a 5 digit number (anything but a PO or Work Order) the SUBSTRING()
> function should return null or empty string (not sure of the behavior).
> I really don't get it. I think I have the semantics as to how the script
> should work, but apparently the Sql is getting interpreted incorrectly?
> Is this a bug?

I don't think so. (I will have to admit that I am out on a limb when I
say this, though.) Consider this repro:

CREATE TABLE [Document] (
IDDocument  uniqueidentifier NOT NULL PRIMARY KEY,
Doctype     tinyint          NOT NULL,
Number      varchar(50)      NOT NULL,
)
go
CREATE  FUNCTION PadNumber (@numberToPad int, @length int)
RETURNS varchar(50) AS
BEGIN
RETURN  (REPLACE(STR(@number
ToPad,@length), SPACE(1),'0'))
END
go
go
INSERT Document (IDDocument, Doctype, Number)
VALUES (newid(), 1, 'ST-00001')
INSERT Document (IDDocument, Doctype, Number)
VALUES (newid(), 2, '00001')
INSERT Document (IDDocument, Doctype, Number)
VALUES (newid(), 2, '00011')
INSERT Document (IDDocument, Doctype, Number)
VALUES (newid(), 3, '00000101')
go
SELECT CASE Doctype
WHEN 1 THEN 'ST-' + dbo.PadNumber(
 COALESCE(MAX(CAST(SU
BSTRING(Number, 4, 6) AS int)), 0) + 1, 6)
WHEN 2 THEN dbo.PadNumber(
 COALESCE(MAX(CAST(Nu
mber AS int)), 0) + 1, 8)
WHEN 3 THEN dbo.PadNumber(
 COALESCE(MAX(CAST(Nu
mber AS int)), 0) + 1, 5)
END
FROM  Document
GROUP BY Doctype
go
DROP TABLE Document
DROP FUNCTION PadNumber

Here I have replaced the variable by a column. Assume that the CASE would
work as you want it to. Then SQL Server would traverse the table, and
then for each row compute the MAX value required - which would mean
yet another iteration over the table.

Thus, it makes more sense to first compute the aggregated table with
all the MAX expressions, and then do the column list from there. To
address this you would have to swap MAX and CAST as I suggested in
my previous post. Or change the MAX expression to:

MAX(CAST(CASE Doctype WHEN THEN 2 Number END) as int)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks, Erland. I appreciate your help. :)

Jeff...

> Jeff Gilbert  (blackhole@gilbertne
t.net) writes:
> 
> I don't think so. (I will have to admit that I am out on a limb when I
> say this, though.) Consider this repro:
>
> CREATE TABLE [Document] (
> IDDocument  uniqueidentifier NOT NULL PRIMARY KEY,
> Doctype     tinyint          NOT NULL,
> Number      varchar(50)      NOT NULL,
> )
> go
> CREATE  FUNCTION PadNumber (@numberToPad int, @length int)
> RETURNS varchar(50) AS
> BEGIN
> RETURN  (REPLACE(STR(@number
ToPad,@length), SPACE(1),'0'))
> END
> go
> go
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 1, 'ST-00001')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 2, '00001')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 2, '00011')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 3, '00000101')
> go
> SELECT CASE Doctype
> WHEN 1 THEN 'ST-' + dbo.PadNumber(
>  COALESCE(MAX(CAST(SU
BSTRING(Number, 4, 6) AS int)), 0) + 1,
> 6)
> WHEN 2 THEN dbo.PadNumber(
>  COALESCE(MAX(CAST(Nu
mber AS int)), 0) + 1, 8)
> WHEN 3 THEN dbo.PadNumber(
>  COALESCE(MAX(CAST(Nu
mber AS int)), 0) + 1, 5)
> END
> FROM  Document
> GROUP BY Doctype
> go
> DROP TABLE Document
> DROP FUNCTION PadNumber
> Here I have replaced the variable by a column. Assume that the CASE
> would
> work as you want it to. Then SQL Server would traverse the table, and
> then for each row compute the MAX value required - which would mean
> yet another iteration over the table.
> Thus, it makes more sense to first compute the aggregated table with
> all the MAX expressions, and then do the column list from there. To
> address this you would have to swap MAX and CAST as I suggested in my
> previous post. Or change the MAX expression to:
>
> MAX(CAST(CASE Doctype WHEN THEN 2 Number END) as int)
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...ownloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>