 # Function for new order number

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

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')`

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

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