CASE problem

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


.