Hey guys. I posted this on the newsgroups, but thought I'd give you beautiful people a try. I hope I don't come out looking too dumb on this one...

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, 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. What am I missing?

Thanks in advance for any help you can give.

Jeff...

I hope I don't sound stupid, but may I ask, what does the SELECT selects?

shouldn't it be something like....

SELECT ReturnField =
    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, 
    ELSE dbo.PadNumber(COALESCE(MAX(CAST(Number AS int)), 0) + 1, 5)
    END

Walaa wrote:

I hope I don't sound stupid, but may I ask, what does the SELECT selects?

shouldn't it be something like....

SELECT ReturnField =
    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, 
    ELSE dbo.PadNumber(COALESCE(MAX(CAST(Number AS int)), 0) + 1, 5)
    END

Well the actual field is designated in the return_expression. The version I posted doesn't alias it, but something is getting SELECTed.

Jeff...

jeffreygg wrote:

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. What am I missing?

I think this is the clue. My guess is that you'll need to use the "with recompile" option on so that it recompiles on each use. Since you're returning essentially two types of data, it has to recompile each time it is run so that it can return the type it was given. If the first time you run it, it compiles to return an int, then forever an int it expects to return.

But that's just my guess.

NickD wrote:

jeffreygg wrote:

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. What am I missing?

I think this is the clue. My guess is that you'll need to use the "with recompile" option on so that it recompiles on each use. Since you're returning essentially two types of data, it has to recompile each time it is run so that it can return the type it was given. If the first time you run it, it compiles to return an int, then forever an int it expects to return.

But that's just my guess.

I'll give that a try, but PadNumber() returns a varchar(50). I got a similar response on the newsgroups, that each branch returns a different data type, but I don't think that's the case; each branch's final call is to PadNumber, except for "1" which simply appends a "ST-" to it.

Jeff...

No, that didn't work. Also, it isn't immediately obvious from the information I posted, but "ST-000001" is the INPUT value into each branch as it's the MAX value in the column. It's not the output value that's getting hung up. Remember, the first time it works, but the second time fails, so the output for the second time would be "ST-000002".

Jeff...

Ok, I resolved the problem, but I'm still not sure why it happens:

SELECT
    --Work Order: 8 digits
    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

I added SUBSTRING(Number, 4, 6) to each branch, even though I think that that should break it. However, it actually fixes it. If anyone can explain to me why this is happening I would be much obliged.

Jeff...