Variable length strings as function parameters

Hi Guys.

I am attempting to modify existing client side code to use server side functions and procedures. I am wondering if it is possible to use a variable length input parameter in a function. The incoming value can be from 1 to 4 characters in length. The function will evaluate the length of the parameter and the last character of the parameter and modify it based on what it finds. The resulting value may increase in size based on what is found. The result will either stay the same size or increase in size by 1 character and will never be bigger than 4 characters. One the client side this value is treated as a string and I use Len() and Right() to preform processing. Since there are no strings in Server per say (Forgive what may be a poor choice of words) I am wondering if this type of processing can be done as a server side function. Any help you can provide is appreciated.

please provide some sample data and sampling of possible parameters coming from client side?

This is the original VB.Net function:
Private Function UpdatePrefix(ByVal sPrefix As String) As String

  Dim sLastChar As String
  Dim sNextChar As String

  Select Case Len(sPrefix)
     Case 1
        sPrefix = sPrefix & "A"
     Case 2, 3
        sLastChar = Strings.Right(sPrefix, 1)
        If sLastChar = "Z" Then
           sPrefix = sPrefix & "A"
        Else
           sNextChar = NextLetter(sLastChar)
           sPrefix = Mid(sPrefix, 1, Len(sPrefix) - 1) & sNextChar
        End If
     Case 4
        sLastChar = Strings.Right(sPrefix, 1)
        If sLastChar = "Z" Then
           sPrefix = "****"
        Else
           sNextChar = NextLetter(sLastChar)
           sPrefix = Mid(sPrefix, 1, Len(sPrefix) - 1) & sNextChar
        End If
  End Select

  UpdatePrefix = sPrefix

End Function

As an example the incoming function parameter could be any letter of the alphabet. The prefix is altered when the max counter value is reached. When the counter is first created it's max value is 999. Once that max value is reached the prefix is altered A prefix of A becomes AA and the max counter value becomes 99. When that counter is maxed the prefix becomes AB and so on. The process is similar to an SQL Sequence. The counters values are maintained in a table which is part of my database. This process is used to generate key values for the records in my primary table. The prefix value is determined by the first letter of the name of trading card. For instance Pyramid of Light. The prefix would be P. I hope this explanation and background information is sufficient.

From your description, I came up with:

create function vscurtis(@numval int,@prefix varchar(4))
returns table
as
return (
   select case
             when (len(@prefix)<=1 and @numval<999) or @numval<99
             then @numval+1
             else 1
          end as numval
         ,case
             when len(@prefix)=0
             then 'A'
             when (len(@prefix)=1 and @numval<999) or @numval<99
             then @prefix
             when len(@prefix)<=1
               or (len(@prefix)<4
              and  right(@prefix,1)='Z'
                  )
             then @prefix+'A'
             when len(@prefix)=4
              and right(@prefix,1)='Z'
             then '****'
             else left(@prefix,len(@prefix)-1)
                 +char(ascii(right(@prefix,1))+1)
          end as prefix
)
go

/* Testing function */
with cte(numval,prefix)
  as (select *
        from (values(1,'A')
                   ,(99,'A')
                   ,(998,'A')
                   ,(999,'A')
                   ,(98,'AA')
                   ,(99,'AA')
                   ,(98,'AZ')
                   ,(99,'AZ')
                   ,(98,'AZA')
                   ,(99,'AZA')
                   ,(98,'AZZ')
                   ,(99,'AZZ')
                   ,(98,'AZZA')
                   ,(99,'AZZA')
                   ,(98,'AZZZ')
                   ,(99,'AZZZ')
             ) as cte(numval,prefix)
     )
select a.numval as old_numval
      ,a.prefix as old_prefix
      ,b.numval as new_numval
      ,b.prefix as new_prefix
  from cte as a
       cross apply vscurtis(a.numval,a.prefix) as b
;

Thanks for your assistance. As I am looking at what you have provided here, I do not think it will duplicate the function of my existing code. I am working on coding my attempt at converting the code now. If you like I can post it when I done.

Have you tried?

Your VB function doesn't handle the counter you mentioned, other than that, I just followed your description.

My description was general not specific so it's unlikely that your code will duplicate it's function. There are other functions that maintain the counter table. The function in question is one part of a larger whole.

I see. So maybe this is what you want:

create function vscurtis(@prefix varchar(4))
returns table
as
return (
   select case
             when len(@prefix)=1
               or (len(@prefix)<4
              and  right(@prefix,1)='Z'
                  )
             then @prefix+'A'
             when len(@prefix)=4
              and right(@prefix,1)='Z'
             then '****'
             else left(@prefix,len(@prefix)-1)
                 +char(ascii(right(@prefix,1))+1)
          end as prefix
)
go

/* Testing function */
with cte(prefix)
  as (select *
        from (values('A')
                   ,('AA')
                   ,('AZ')
                   ,('AZA')
                   ,('AZZ')
                   ,('AZZA')
                   ,('AZZZ')
             ) as cte(prefix)
     )
select a.prefix as old_prefix
      ,b.prefix as new_prefix
  from cte as a
       cross apply vscurtis(a.prefix) as b
;

I will try to explain this function's purpose to you more clearly. This function executes when a counter has reached its max value. When a counter record is first created its max value is 999 and the prefix is a single character. When the counter reaches it's first max value the counter record is modified. The counter's new max value is 99 and the prefix is modified by appending an 'A'. When the new counter prefix combination reaches it's max value the 'A' that was appended becomes a 'B', and 'C' and so on until the appended value becomes a 'Z'. When this happens the counter and prefix are both modified again the counter's max value becomes 9 and another 'A' is appended to the end of the prefix. This process continues until all possible unique values have been used. At which point the prefix is set to '****'. Since my key field is 4 characters long a maxed out counter might look like 'AZZZ' Indicating that this counter managed record's who key began with 'A' So you see the prefix will never have a length of 0. It will always be a least 1 character in length and will increase in size by 1 character when the counter used with the prefix reaches it's max value. I hope my explanation further clarifies the codes purpose.

this relates to this thread and I had difficulty understanding it maybe someone else can help.

Yes the two posts are related. The code provided manages the Prefix portion of the key. The counters are maintained in a table that is part of my database. They behave similar to sequences but have custom behaviors that I attempted to describe to you. Each counter has a name example tblCatds_A. The name indicates which table the counter belongs to and what letter of the alphabet it will generate keys for. In this example the counter belongs to tblCards and all keys generated by this counter will begin with the letter A. In my database the key for the primary table is the name of the trading card. The first letter of the name of the trading card is used as the basis for the prefix. Certain exclusions are made such as the words A An and The are ignored and special characters or numbers. Your solution of generating all possible keys and storing them is not an acceptable solution. I appreciate your assistance. I am in the process of converting smaller client side functions to server side. I'm not at a point where I can test anything.

I'm still not sure I understand what exactly your function should do.
But try this:

create function vscurtis(@prefix varchar(4))
returns table
as
return (
   with prefixsplit(prefix,letter,number)
     as (select @prefix
               ,case
                   when patindex('%[A-Z]%','0'+reverse(@prefix))=0
                   then null
                   else reverse(right(right('0000'+reverse(@prefix),4)
                                     ,5-patindex('%[A-Z]%'
                                                ,right('0000'+reverse(@prefix),4)
                                                )
                                     )
                               )
                end as letter
               ,case
                   when patindex('%[0-9]%','A'+@prefix)=0
                   then null
                   else substring(@prefix
                                 ,patindex('%[0-9]%','A'+@prefix)-1
                                 ,len(@prefix)-patindex('%[0-9]%','A'+@prefix)+2
                                 )
                end as number
        )
   select case
             when isnull(number,0)+1<power(10,4-len(isnull(letter,'')))
             then isnull(letter,'')
             when letter is null
               or (len(letter)=4
              and  right(letter,1)='Z'
                  )
             then '****'
             when right(letter,1)='Z'
               or len(letter)=1
             then letter+'A'
             else left(letter,len(letter)-1)
                 +char(ascii(right(isnull(letter,' '),1))+1)
          end
         +case
             when isnull(number,0)+1<power(10,4-len(isnull(letter,'')))
             then ltrim(str(isnull(number,0)+1))
             when len(isnull(letter,'****'))<4
             then '1'
             else ''
          end as prefix
     from prefixsplit
)
go

And for testing purpose:

with cte(prefix)
  as (select *
        from (values('')
                   ,('1')
                   ,('9')
                   ,('10')
                   ,('18')
                   ,('19')
                   ,('98')
                   ,('99')
                   ,('101')
                   ,('998')
                   ,('999')
                   ,('9998')
                   ,('9999')
                   ,('A')
                   ,('A1')
                   ,('A8')
                   ,('A9')
                   ,('A98')
                   ,('A99')
                   ,('A998')
                   ,('A999')
                   ,('AA99')
                   ,('AAA9')
                   ,('AAB9')
                   ,('AABA')
                   ,('AZZY')
                   ,('AZZZ')
                   ,('ZZZA')
                   ,('ZZZY')
                   ,('ZZZZ')
             ) as cte(prefix)
     )
select a.prefix as old_prefix
      ,b.prefix as new_prefix
  from cte as a
       cross apply vscurtis(a.prefix) as b
;

Would it help to see output from a counter from start to finish? I don't think your suggested code will do the job. Looking, no test data is required. The output for this function is a modified prefix that is then concatenated with a sequence value to produce a unique key. I'm not sure what other information I can provide.

yes please! This has confused enough people to warrant a detailed sample start to finish. please start with lowest.

  1. What is the lowest seed counter? is it numeric or alpha numeric?

I will execute the code using the original MS Access database and code so you can see the results of a single counter being created and used to generate a key from record one until the counter has exhausted itself. That way you can see how and when the prefix is modified as keys are created. Can files be posted to this site? It would be easier to post a database with a test table containing the keys rather than a list. Each counter is capable of generating 4000+ unique values using my method. The lowest counter value is always 1. It is numeric and is formatted with leading zeroes. Lets cay it's the first record for the 'A' counter. The first key generated would be A001 the next is A002 and so on until the counter reaches its max value of 999. Then the next key becomes AA01 and continues until the counter reaches its max value of 99 then the next key becomes AC01 and so on until finally the key becomes AZZZ. I will prepare an example of output. Please let me know my options for posting this output.

try this. it is a start but not fully implemented yet. got some issues. what an ugly function!!

/*
/*
	if exists (select 1 from sys.tables where name = 'productTracker')
	begin
		drop table productTracker
	end
	go

	create table dbo.productTracker(sequence varchar(4) not null);
*/

if exists (select 1 from sys.objects where name = 'ufn_nextSequence')
begin
	drop function ufn_nextSequence
end
go


create function dbo.ufn_nextSequence( @seed varchar(1) )
RETURNS @retFindReports TABLE  
(  
   nextSequence varchar(4)
)
as
begin
	declare @nextSequence varchar(4), @maxforseed varchar(3), @nextseed int;

	if not exists (select 1 from dbo.productTracker where sequence like @seed + '%')
		begin
			select @nextSequence = @seed + '001'
		end
	else
		begin
			;with cteMax
			as
			(
				select case
				          when left(replace (pt.sequence, @seed,''),2) = '00' then  REPLACE( replace (pt.sequence, @seed,''), '0', '')
						  else left(replace (pt.sequence, @seed,''), 4)
				       end  as sequence
				  from dbo.productTracker  pt
				 where pt.sequence like @seed + '%'
			 )	
			select @maxforseed = MAX(cast(pt.sequence as int) )	   			   
			  from cteMax pt


			select @nextSequence = 
			      case
			          when (len(@maxforseed) = 1 and @maxforseed <9) then @seed + '00' + cast(cast(@maxforseed as int) + 1 as varchar(3))
					  when (len(@maxforseed) = 1 and @maxforseed = 9) then @seed + '0' + cast(cast(@maxforseed  as int) + 1 as varchar(3))
					  when (len(@maxforseed) = 2 and @maxforseed < 99) then @seed + '0' + cast(cast(@maxforseed  as int) + 1 as varchar(3))
					  when (len(@maxforseed) = 2 and @maxforseed = 99) then @seed + cast(cast(@maxforseed  as int) + 1 as varchar(3))
					  when ( len(@maxforseed) = 3 and @maxforseed < 999) then @seed + cast(cast(@maxforseed  as int) + 1 as varchar(3))
					  when ( len(@maxforseed) = 3 and @maxforseed = 999) then '****'
				   end
		end

	insert into @retFindReports
	select @nextSequence;
	
	RETURN  
end
GO 


DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag <= 999)
BEGIN
	insert into dbo.productTracker
	select * 
	  From ufn_nextSequence('A');

	 SET @intFlag = @intFlag + 1
END
GO


select * From dbo.productTracker order by sequence desc;