UFN_MKTSOURCECODE_BUILDNEXTVALUE
Builds the next source code value for a given format and code.
Return
| Return Type |
|---|
| nvarchar(10) |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FORMAT | nvarchar(10) | IN | |
| @CODE | nvarchar(10) | IN |
Definition
Copy
CREATE function dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE]
(
@FORMAT nvarchar(10),
@CODE nvarchar(10)
)
/*
Takes a source code part value and a format and builds the next code in sequence.
*/
returns nvarchar(10)
as begin
declare @SOURCECODEPART nvarchar(10);
declare @CODECHAR char;
declare @FORMATCHAR char;
declare @STARTPOS integer;
declare @INCREMENTNEXT bit; /* Indicates whether or not we need to increment the next character in the code */
set @INCREMENTNEXT = 1;
set @STARTPOS = LEN(@FORMAT);
set @FORMATCHAR = substring(@FORMAT, @STARTPOS, 1);
set @CODECHAR = substring(@CODE, @STARTPOS, 1);
set @SOURCECODEPART = '';
/* Loop backwards through the code and format */
while (@STARTPOS > 0)
begin
declare @NEXTCHAR char;
if @INCREMENTNEXT = 1
begin
if (@FORMATCHAR = '#') -- number
begin
if (@CODECHAR = '9' or @CODECHAR = '')
begin
set @NEXTCHAR = '0';
set @INCREMENTNEXT = 1;
end
else
begin
set @NEXTCHAR = convert(char, convert(tinyint, @CODECHAR) + 1);
set @INCREMENTNEXT = 0;
end
end
else if (@FORMATCHAR = '@') -- alpha
begin
if (@CODECHAR = 'Z' or @CODECHAR = '')
begin
set @NEXTCHAR = 'A';
set @INCREMENTNEXT = 1;
end
else
begin
set @NEXTCHAR = char(ascii(@CODECHAR) + 1);
set @INCREMENTNEXT = 0;
end
end
else if (@FORMATCHAR = '?') -- number or alpha
begin
if (@CODECHAR = '9')
begin
set @NEXTCHAR = 'A';
set @INCREMENTNEXT = 0;
end
else if (@CODECHAR = '')
begin
set @NEXTCHAR = '0';
set @INCREMENTNEXT = 1;
end
else if (@CODECHAR = 'Z')
begin
set @NEXTCHAR = '0'
set @INCREMENTNEXT = 1;
end
else if (isnumeric(@CODECHAR) = 1)
begin
set @NEXTCHAR = convert(char, convert(tinyint, @CODECHAR) + 1);
set @INCREMENTNEXT = 0;
end
else -- Is alpha character
begin
set @NEXTCHAR = char(ascii(@CODECHAR) + 1);
set @INCREMENTNEXT = 0;
end
end
else
begin
set @NEXTCHAR = @FORMATCHAR;
set @INCREMENTNEXT = 1;
end
end
else
set @NEXTCHAR = @CODECHAR;
set @STARTPOS = @STARTPOS - 1;
set @FORMATCHAR = substring(@FORMAT, @STARTPOS, 1);
set @CODECHAR = substring(@CODE, @STARTPOS, 1);
if (@CODECHAR = '')
set @INCREMENTNEXT = 1;
set @SOURCECODEPART = @NEXTCHAR + @SOURCECODEPART;
end
return @SOURCECODEPART;
end