|
|
Posted by Jeff Gilbert on 11/28/05 09:17
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...
Navigation:
[Reply to this message]
|