October 8, 2012 at 8:16 am
Am using below stored procedure to display alpha-numeric sequence numbers.
CREATE PROCEDURE tsp_alpha_num_test
as
begin
SELECT a.Chr+b.Chr+c.Chr AS AlphaNumeric
FROM (VALUES
('SPA-'))a(Chr)
CROSS JOIN (VALUES
('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'),
('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'),
('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z'))b(Chr)
CROSS JOIN (VALUES
('10'),('20'),('30'),('40'),('50'),('60'), ('70'),('80'),('90'))c(Chr)
ORDER BY a.Chr, b.Chr
END
my requirement is, when i call above stored proc 1st time it should display SPA-010,
2nd time SPA-020,
every time when i call the stored proc, next value should be displayed.
above stored procedure may be called from any function, or stored proc.
Plz help...
October 8, 2012 at 8:27 am
You will need to persist the values which have already been generated, or read them from the table in which they are being used.
Create a table outside of the stored procedure. Then, within the stored procedure, alter the query to return the topmost value from the list into a variable, and to filter out values which already exist in the table. Insert the value into the table, and output the variable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 8:38 am
Skanda (10/8/2012)
Am using below stored procedure to display alpha-numeric sequence numbers.CREATE PROCEDURE tsp_alpha_num_test
as
begin
SELECT a.Chr+b.Chr+c.Chr AS AlphaNumeric
FROM (VALUES
('SPA-'))a(Chr)
CROSS JOIN (VALUES
('0'),('1'),('2'),('3'),('4'),('5'),('6'), ('7'),('8'),('9'),('A'),('B'),('C'),('D'),
('E'),('F'),('G'),('H'),('I'),('J'),('K'), ('L'),('M'),('N'),('O'),('P'),('Q'),('R'),
('S'),('T'),('U'),('V'),('W'),('X'),('Y'), ('Z'))b(Chr)
CROSS JOIN (VALUES
('10'),('20'),('30'),('40'),('50'),('60'), ('70'),('80'),('90'))c(Chr)
ORDER BY a.Chr, b.Chr
END
my requirement is, when i call above stored proc 1st time it should display SPA-010,
2nd time SPA-020,
every time when i call the stored proc, next value should be displayed.
above stored procedure may be called from any function, or stored proc.
Plz help...
Are you designing a bottleneck for your system?
BTW, you will not be able to call it from "any functions" as you cannot call stored procs from UDF's.
If you could pay attention to the thread I've pointed you in my previous post, you would find how to encode incrementing numeric numbers into alpha-numeric 4-character code, which is kind of thing you are trying to do...
Why did you create a duplicate thread?
Her is the link: http://www.sqlservercentral.com/Forums/Topic1267659-391-1.aspx
October 8, 2012 at 9:00 am
Ok, you may find it difficult to grab the idea of what you need to from the thread about n-base encoding.
Here we go. You can create the following UDF:
CREATE FUNCTION dbo.f_Based36Encode (@value BIGINT)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
-- some variables
DECLARE @characters CHAR(36),
@result VARCHAR(50);
-- encoding string and the default result
SELECT @characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@result = '';
IF @value IS NULL SELECT @result = NULL
-- encode value to Base36
WHILE @value > 0
SELECT @result = SUBSTRING(@characters, @value % 36 + 1, 1) + @result,
@value = @value / 36;
RETURN @result;
END
This UDF will convert any number (upto bigint) into 36-based numerical system (aka 16-based HEX)
You can use as a base for getting 4-character codes for your incrementing numbers.
The UDF from the old thread, does a bit more - it takes previous encoded value and gives you the next one, so you could pass there 'Z' and get back '10', so it would allow you to do kind of what you want, but you will need to pass previous value into it.
Or, you can just use IDENTITY column to generate incrementing numbers and have your "Code" column as computed one or populated by insert-trigger which will call f_Based36Encode.
Here is example which will show what you may get using provided function:
select 'SPA-'+ RIGHT('0000' + dbo.f_Based36Encode (row_number() over (order by object_id)), 4)
from sys.columns
October 8, 2012 at 9:27 am
Hi,
Function is working fine,
but when ever i call the function, it should return single value only,
if i call 10 times - 10 alphanumeric values should be written in a sequence with out duplicate.
if i call 11th time - 11th sequence number should be written
Please help........
October 8, 2012 at 9:46 am
Skanda (10/8/2012)
Hi,Function is working fine,
but when ever i call the function, it should return single value only,
if i call 10 times - 10 alphanumeric values should be written in a sequence with out duplicate.
if i call 11th time - 11th sequence number should be written
Please help........
You need to understand how this function works.
If you pass the same numeric value into it it will return exactly the same base36 encoded value back.
You need to pass into the function incrementing numeric values.
Or you can use dbo.f_GetNextBased36Value function from mentioned thread http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx, but you will need to pass previous value into it.
SQL Server UDF or Stored Procedure cannot preserve any values by themselves.
If you need more help, please provide more details about what you are trying to do and design, then, hopefully, we can advise you about some possible solutions.
October 8, 2012 at 9:48 am
Skanda (10/8/2012)
Hi,Function is working fine,
but when ever i call the function, it should return single value only,
if i call 10 times - 10 alphanumeric values should be written in a sequence with out duplicate.
if i call 11th time - 11th sequence number should be written
Please help........
ChrisM@Work (10/8/2012)
You will need to persist the values which have already been generated, or read them from the table in which they are being used.Create a table outside of the stored procedure. Then, within the stored procedure, alter the query to return the topmost value from the list into a variable, and to filter out values which already exist in the table. Insert the value into the table, and output the variable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply