How to generate custom serial number from dynamic sql and return

Question

Create function NextCustomerNumber
(
    @TABLE_NAME varchar(20)
)
returns varchar(10)
as
begin

    declare @lastval varchar(10)
    set @lastval = right('000000000' + convert(varchar(10),(select IsNull(max(Serialno),0)+1 from TestSerialNo)),10)
    return @lastval
end

OR

create procedure test 
    (@TABLE_NAME varchar(20))
as
    declare @lastval varchar(10)

    set @lastval = right('000000000' + convert(varchar(10),
                          (select IsNull(max(Serialno), 0) + 1 
                           from @TABLE_NAME)), 10)
    return @lastval
end

please tell me how could i return serial no to its calling environment make the sql dynamic inside function not store procedure.

Answer 1

Hi @Sudip Bhatt ,

You could not use dynamic SQL inside a user-defined function in SQL Server. Stored procedures are able to change the table data while user-defined functions are not.

But If you insist on using functions, please refer below:

Firstly, create one table and one serial number column as primary key .

create table testA  
(Serialno int identity(1,1) primary key,  
name varchar(1000))  
  
DECLARE @i INT  
SET @i = 1  
WHILE (@i <= 300)  
BEGIN  
INSERT INTO testA VALUES(CONCAT('name_', @i))  
SET @i = @i + 1  
END  
select * from testA  

Secondly create the function like below:

create function NextCustomerNumber  
 (  
     @TABLE_NAME varchar(20)  
 )  
 returns varchar(1000)  
 as  
 begin  
     declare @column_name varchar(100)  
  SELECT @column_name=COLUMN_NAME  
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1  
 AND TABLE_NAME = @TABLE_NAME   
  
 declare @lastval varchar(1000)  
     set @lastval = 'select right(''000000000'' + convert(varchar(10),(select IsNull(max('+@column_name+'),0)+1 from '+@TABLE_NAME+')),10)'  
     return @lastval  
 end  

Then we could get the statement by calling above function like below:

 select dbo.NextCustomerNumber ('testa')  

Output:

select right('000000000' + convert(varchar(10),(select IsNull(max(Serialno),0)+1 from testa)),10)  

Finally we could run above output query and get the output as below:
Output:
0000000301

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You cannot use dynamic SQL inside a function, period.

But why is the table name dynamic in the first place? Dynamic table names are often a token of an incorrect design.

Answer 3

Hi @Sudip Bhatt ,

Maybe you could have a try with CREATE SEQUENCE.

For example:

CREATE SEQUENCE dbo.DecSeq    
    AS decimal(3,0)     
    START WITH 125    
    INCREMENT BY 25    
    MINVALUE 100    
    MAXVALUE 200    
    CYCLE    
    CACHE 3    
  
   SELECT NEXT VALUE FOR dbo.DecSeq;   
   SELECT NEXT VALUE FOR dbo.DecSeq;   
   SELECT NEXT VALUE FOR dbo.DecSeq;   
  
SELECT name,start_value,increment,minimum_value,maximum_value,current_value,last_used_value FROM sys.sequences  

You could also refer more details from below:
The Basics of Using a Sequence as a Table Default

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.