CASE problem

[Jeff Gilbert]

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

First the DDL:

CREATE TABLE [Document] (
[IDDocument] [uniqueidentifier] NOT NULL ,
[IDParentDocument] [uniqueidentifier] NULL ,
[IDDocumentType] [uniqueidentifier] NOT NULL ,
[Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDT] [datetime] NOT NULL CONSTRAINT [DF_Document_CreationDate] DEFAULT
(getdate()),
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
[IDDocument]
) ON [PRIMARY] ,
CONSTRAINT [IX_Document] UNIQUE NONCLUSTERED
(
[Number],
[IDDocumentType]
) ON [PRIMARY] ,
CONSTRAINT [FK_Document_Document] FOREIGN KEY
(
[IDParentDocument]
) REFERENCES [Document] (
[IDDocument]
),
CONSTRAINT [FK_Document_DocumentType] FOREIGN KEY
(
[IDDocumentType]
) REFERENCES [DocumentType] (
[IDDocumentType]
)
) ON [PRIMARY]

CREATE TABLE [DocumentType] (
[IDDocumentType] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_DocumentType_IDDocumentType]
DEFAULT (newid()),
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DocumentType] PRIMARY KEY CLUSTERED
(
[IDDocumentType]
) ON [PRIMARY]
) ON [PRIMARY]
Next, information:

"Document" is the root type in an inheritance heirarchy which includes sub-types
such as "Purchase Order", "Requisition", "Work Order", and so on. Each document
type has it's own numbering scheme for it's identifying number (the PO number,
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 GetNextInSequenceStockton
@documentType int
AS

SELECT
CASE @documentType
WHEN 1 THEN 'ST-' + dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4,
6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(Number AS int)), 0) + 1, 8)
ELSE dbo.PadNumber(COALESCE(MAX(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(@numberToPad,@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 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.

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 there'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 prefix)
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...

[Erland Sommarskog]

Jeff Gilbert (bl*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Gilbert]

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 (bl*******@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/pro...ownloads/books.
mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Gilbert]

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 (bl*******@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/pro...ownloads/books.
mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Gilbert]

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(MAX(CAST(SUBSTRING(Number, 4,
6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6) AS int)),
0) + 1, 8)
ELSE dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,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 (bl*******@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/pro...ownloads/books.
mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Jeff Gilbert (bl*******@gilbertnet.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(MAX(CAST(SUBSTRING(Number,
4, 6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6)
AS int)), 0) + 1, 8)
ELSE dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Gilbert]

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?

Jeff...
Jeff Gilbert (bl*******@gilbertnet.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(MAX(CAST(SUBSTRING(Number,
4, 6) AS int)), 0) + 1, 6)
WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6)
AS int)), 0) + 1, 8)
ELSE dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,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.
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ownloads/books.
mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Jeff Gilbert (bl*******@gilbertnet.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(@numberToPad,@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(SUBSTRING(Number, 4, 6) AS int)), 0) + 1, 6)
WHEN 2 THEN dbo.PadNumber(
COALESCE(MAX(CAST(Number AS int)), 0) + 1, 8)
WHEN 3 THEN dbo.PadNumber(
COALESCE(MAX(CAST(Number 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff Gilbert]

Thanks, Erland. I appreciate your help. :)

Jeff...

Jeff Gilbert (bl*******@gilbertnet.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(@numberToPad,@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(SUBSTRING(Number, 4, 6) AS int)), 0) + 1,
6)
WHEN 2 THEN dbo.PadNumber(
COALESCE(MAX(CAST(Number AS int)), 0) + 1, 8)
WHEN 3 THEN dbo.PadNumber(
COALESCE(MAX(CAST(Number 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/pro...ownloads/books.
mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx