Determine next order id

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)

Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003

I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).

I honestly don't know where to begin.

Can someone please help?

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(OderNumber,3) + 1 AS VARCHAR(6)),3)
FROm Orders
Order by OrderNumer
Where CustId = @.CustId

SELECT @.LASTOrdernNum = ISNULL(@.LASTOrdernNum,'001')

SELECT CAST(CustId as VARCHAR(4)) + '-' + LASTOrdernNum

Should b something like the above (untested).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

That was very close. Thanks very much.

Is there a way to return a string value? I won't be calling this SP with ExecuteScalar, so I'd like to RETURN the value instead of just selecting it.

Here's the working version:

Code Snippet

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

I can use RETURN on the last line instead of select, but my test code errors with:

Conversion failed when converting the varchar value '10052-002' to data type int.

|||

RETURN allows for only an integer operand, which is generally used as a status or return code.

You may want to consider creating a function rather than a procedure.

|||

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

@.retval varchar(255) output

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT @.retval = CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

sql