Function for new order number

Transact-SQL
1

Hello:

I would like to create a function that returns a new order number based on the parameter that I give it for a previous order number. Here is an example:
Old order number: RS001-00000013
New order number desired from function: RS001-00000014

The "RS001" means "Retail Sale" from location number "001". All of these types of orders that I want to run through this will begin with "RS".

I would like to insert a parameter value of RS001-00000013 and get RS001-00000014.

Alternatively I would like to enter a value of RS002-00000009 and get RS002-00000010.

I had some ideas about a scalar-value function, but I'm pretty new to functions. I'm pretty experienced with T-SQL statements in general, but the syntax of functions wierds me out because I haven't been exposed to them very much.

Anyone have any ideas?

Thank you in advance for any and all suggestions.

Ben

2

Rather than create a function, use the code in-line, as in the example below:

DECLARE @CurrentId VARCHAR(32) = 'RS001-00000013'
SELECT
	L+RIGHT( REPLICATE('0',LEN(R))+CAST(CAST(R AS BIGINT)+1 AS VARCHAR(32)), LEN(R))
		AS NextId
FROM
	(VALUES (STUFF(@CurrentId,1,CHARINDEX('-',@CurrentId+'-'),''),
		LEFT(@CurrentId,CHARINDEX('-',@CurrentId+'-')))) A(R,L)

If you must have it as a function, instead of creating a scalar function, create an in-line table valued function like shown below.

CREATE  FUNCTION [dbo].[GetNextId]
(
	@CurrentId VARCHAR(32)
)
RETURNS TABLE
AS
RETURN
(
	SELECT
		L+RIGHT( REPLICATE('0',LEN(R))+CAST(CAST(R AS BIGINT)+1 AS VARCHAR(32)), LEN(R))
			AS NextId
	FROM
		(VALUES (STUFF(@CurrentId,1,CHARINDEX('-',@CurrentId+'-'),''),
			LEFT(@CurrentId,CHARINDEX('-',@CurrentId+'-')))) A(R,L) 
)
GO

Then use it like this:
SELECT NextId FROM dbo.GetNextId('RS001-00000013')

3

Yes, it looks like the non-function T-SQL statement works great. Thank you!

4

CREATE FUNCTION [dbo].[GetNextId]
(
@CurrentId VARCHAR(32)
)
RETURNS TABLE
AS
RETURN
(
SELECT
L+RIGHT( REPLICATE('0',LEN(R))+CAST(CAST(R AS BIGINT)+1 AS VARCHAR(32)), LEN(R))
AS NextId
FROM
(VALUES (STUFF(@CurrentId,1,CHARINDEX('-',@CurrentId+'-'),''),
LEFT(@CurrentId,CHARINDEX('-',@CurrentId+'-')))) A(R,L)
)
GO

RIGHT( REPLICATE('0',LEN(R))+CAST(CAST(R AS BIGINT)+1 AS VARCHAR(32)), LEN(R)
FOR 00000031

LEN( 00000031) RESULTS 2
I.E 00+31+1
32
BUT WE NEED AS 00000032 FORMAT

HOW INTERNALLY ITS WORKING

PLEASE HELP ME