Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My table 'LoadData' is as below:

Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | S
 19AC          | 310003 |         2019 | 2019-02-10 | S
 NULL          | 310004 |         2019 | 2019-02-20 | A


I have written a SQL function 'GetNextSequenceCode_TEST' which generates the next sequence code,
For example: if we give 'AB' as Input, the function will generate 'AC' as output.

Sequence_Code is Alphanumeric,so i am supposed to consider last two Character of Sequence_Code .
Please look at the below code:

SQL
DECLARE @SeQCode varchar(100)   
SELECT top 1 @SeQCode = SubString(Sequence_Code,3,4)   
FROM LoadData where ID=310003

print  @SeQCode


Here value of the variable @SeQCode i am getting is AC.

But when i send this variable to my function, like this:

SQL
select GetNextSequenceCode_TEST (@SeQCode )


it's giving output B!, which is wrong,

I am supposed to get AD as output.

And when i pass input like

SQL
select GetNextSequenceCode_TEST ('AC')


It gives me Correct output. i.e AD

Please correct me if i am doing something Wrong. or passign input in wrong way

What I have tried:

Function:
SQL
CREATE FUNCTION [dbo].[GetNextSequenceCode_TEST] (@LastSeqAlphabet varchar(100))
  RETURNS varchar(100)
   AS
    BEGIN 
	     DECLARE @fPart varchar(100), @lChar CHAR(1);  
				  IF(@LastSeqAlphabet IS NULL OR @LastSeqAlphabet = '')   
			       RETURN 'A';      

			         SELECT @fPart = LEFT(@LastSeqAlphabet, LEN(@LastSeqAlphabet) - 1);  
					 SELECT @lChar = RIGHT(@LastSeqAlphabet, 1);   
					   
				  IF(@lChar = 'Z')  
				  RETURN (SELECT [dbo].[GetNextSequenceCode_TEST](@fPart)) + 'A';  
				   
		      RETURN @fPart + CHAR(ASCII(@lChar)+1);  
	   END


I have tried this too:
SQL
select  dbo.GetNextSequenceCode_TEST  ('''' + @SeQCode + '''')


But getting a wrong output.
Posted
Updated 5-Jan-21 22:44pm
v3
Comments
Member 15036870 6-Jan-21 1:59am    
CREATE FUNCTION [dbo].[GetNextSequenceCode_TEST] (@LastSeqAlphabet varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @fPart varchar(100), @lChar CHAR(1);
IF(@LastSeqAlphabet IS NULL OR @LastSeqAlphabet = '')
RETURN 'A';

SELECT @fPart = LEFT(@LastSeqAlphabet, LEN(@LastSeqAlphabet) - 1);
SELECT @lChar = RIGHT(@LastSeqAlphabet, 1);

IF(@lChar = 'Z')
RETURN (SELECT [dbo].[GetNextSequenceCode_TEST](@fPart)) + 'A';

RETURN @fPart + CHAR(ASCII(@lChar)+1);
END
Member 15036870 6-Jan-21 2:00am    
This is the function
Member 15036870 6-Jan-21 2:20am    
@Maciej Los, Any Suggestion/Solution for this?

Your function is wrong. You should use sql code similar to that i posted in this threat: How can we write a SQL function to increment alphabetic 'sequence_code'[^]

Take a look at below comments and change your function accordingly
SQL
-- @LastSeqAlphabet possible values: 'AA', ..., 'AZ', 'BA', 'BB' and so on...
CREATE FUNCTION [dbo].[GetNextSequenceCode_TEST] (@LastSeqAlphabet varchar(2))
  RETURNS varchar(2)
   AS
    BEGIN 
        --IF @LastSeqAlphabet IS NULL
        --    RETURN 'AA' --initial code
        --ELSE
        --BEGIN
        --    DECLARE @SumOfDigits = SELECT (ASCII(LEFT(@LastSeqAlphabet, 1))-65) + (ASCII(RIGHT(@LastSeqAlphabet, 1))-65) +1
        --    RETURN CHAR((@SumOfDigits) /26 % 26 + 65) + CHAR((@SumOfDigits) % 26 + 65)    
        --END
   END


For further details, please see:
IF...ELSE (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL Server IF ELSE Statement By Examples[^]

As to your other doubts...
SQL
DECLARE @SeQCode varchar(100)   
SELECT TOP 1 @SeQCode = SubString(Sequence_Code,3,4)    --third parameter of substring function should be: 2, because you want to return 2 chars ;)
FROM LoadData where ID=310003

Finally:
SQL
SELECT * FROM dbo.GetNextSequenceCode_TEST(@SeQCode)


More at: CREATE FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs[^]

Tip: You should read the documentation before you ask a question.
 
Share this answer
 
v2
Comments
Member 15036870 6-Jan-21 7:10am    
Thanks @Maciej Los, As suggested by you SubString(Sequence_Code,3,4) was my mistake, that i have corrected,and it started working.. Thanks You so much for all your efforts.
Maciej Los 6-Jan-21 7:12am    
You're very welcome.
thatraja 6-Jan-21 8:19am    
5!
Maciej Los 6-Jan-21 8:32am    
Thanks!
The problem isn't how you are passing the data, it's how you are processing the data.

Basically put your SQL Function doesn't work correctly in all cases.
And since we can't see your function code, we can't help at all.

But you can help yourself - SQL Server Management System includes a debugger: Transact-SQL debugger - T-SQL debugger | Microsoft Docs[^] so use that to look at your function and find out what exactly it is doing!
 
Share this answer
 
Comments
Member 15036870 6-Jan-21 2:01am    
I understand.. that's why i have just posetd the function also,please have a look.. and if possible please suggest,i tried alot,but couldn't find any solution for this..
OriginalGriff 6-Jan-21 2:20am    
What have you tried?
What did the debugger show you?
Member 15036870 6-Jan-21 2:48am    
I couldn't identify the issue,Whether the issue is with my function or the way i am supplying the input
OriginalGriff 6-Jan-21 3:19am    
"What have you tried?
What did the debugger show you?"

Since the debugger will show you exactly what the function is doing while it runs and what it gets as input, it's pretty clear that you haven't even tried to use it ...
Member 15036870 6-Jan-21 3:35am    
I am new to SQL,Not as good as you are in debugging..Anyway Thanks for all your suggestion, I will definitely explore debugging link,which you have shared..when i will have enough time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900