Microsoft SQL Server forum

Author

CASE problem

Jeff Gilbert

2005-11-28, 3:24 am

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 NULL ,
[CreationDT] [datetime] NOT NULL CONSTRAINT & #91;DF_Document_Crea
tionDate] 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 & #91;DF_DocumentType_
IDDocumentType]
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-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 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 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 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 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...

Jeff Gilbert

2005-11-28, 1:23 pm

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
>

Jeff Gilbert

2005-11-28, 1:23 pm

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
>

Jeff Gilbert

2005-11-28, 8:25 pm

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
>

Erland Sommarskog

2005-11-28, 8:25 pm

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

Jeff Gilbert

2005-11-29, 1:23 pm

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 (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
>

Erland Sommarskog

2005-11-29, 8:24 pm

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

Jeff Gilbert

2005-11-30, 8:24 pm

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
>

Sponsored Links