How can we pass varchar input to a SQL function?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2016

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 5-Jan-21 19:30pm

Member 15036870

Updated 5-Jan-21 22:44pm

Maciej Los

v3

Add a Solution

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?

2 solutions

Solution 1

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!

Posted 5-Jan-21 19:50pm

OriginalGriff

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.

Add a Solution

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

Maciej Los · Accepted Answer · 2021-01-05T22:44:00

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.

How can we pass varchar input to a SQL function?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How can we pass varchar input to a SQL function?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us