locked
create function in sql to get uniq number for purchase item RRS feed

  • Question

  • User-807418713 posted

    Hello

    In my table1 i have one batchNo that number i want to generate automtically when data insert to Table1

    Note: Batch No should be unique base on Date & ItemName For Example

    BatchNo Date ItemName Size
    B0001 1-Feb-18 apple Medium
    B0001 1-Feb-18 apple Big
    B0002 2-Feb-18 Mango Normal
    B0003 2-Feb-18 apple Medium
    B0003 2-Feb-18 apple Big
    Saturday, August 11, 2018 9:49 AM

Answers

  • User347430248 posted

    Hi Gopi.MCA,

    You can also try to refer an example below.

    create table temp_so (prikey varchar(100) primary key, name varchar(100))
    go
    
    
    create or alter function dbo.fn_AutoIncrementPriKey_so ()
    returns varchar(100)
    as
    begin
        declare @prikey varchar(100)
        set @prikey = (select top (1) left(prikey,2) + cast(cast(stuff(prikey,1,2,'') as int)+1 as varchar(100)) from temp_so order by prikey desc)
        return isnull(@prikey, 'VV1000')
    end
    go
    
    alter table temp_so
    add constraint df_temp_prikey
    default dbo.[fn_AutoIncrementPriKey_so]() for prikey
    go
    
    insert into temp_so (name) values ('ABC')
    go 5
    
    select * from temp_so

    Output:

    You can try to modify it based on your requirement.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 6:51 AM

All replies

  • User475983607 posted

    Add an identity column that increments.  Create a compound key using the identity, date, and item column.

    Use a query to display the key if need.

    SELECT ('B' + BatchNo + CONVERT(VARCHAR(10), Date, 101) + ItemName) AS Batch
    FROM TheTable

    Saturday, August 11, 2018 12:18 PM
  • User-807418713 posted

    Hello

    I use this query

    create function [dbo].[CHEMSup] (@id int) 
    returns char(9) 
    as 
    begin 
    return 'VV' + right('000000' + convert(varchar(15), @id), 5) 
    end

    how to add this function to my table column

    i want to set permanently prefix vv-00 then after it should go on increment one by one on each record

    Saturday, August 11, 2018 2:01 PM
  • User475983607 posted

    You are making this far more difficult than it is. 

    As explained above, add an Identity column to the table.  This will make the record unique in the table schema.  If you need a human readable label, then simply build the label with a query as shown above.  

    Create a constraint if you require uniqueness by Id, date, and Item.

    Sunday, August 12, 2018 1:25 PM
  • User347430248 posted

    Hi Gopi.MCA,

    You can also try to refer an example below.

    create table temp_so (prikey varchar(100) primary key, name varchar(100))
    go
    
    
    create or alter function dbo.fn_AutoIncrementPriKey_so ()
    returns varchar(100)
    as
    begin
        declare @prikey varchar(100)
        set @prikey = (select top (1) left(prikey,2) + cast(cast(stuff(prikey,1,2,'') as int)+1 as varchar(100)) from temp_so order by prikey desc)
        return isnull(@prikey, 'VV1000')
    end
    go
    
    alter table temp_so
    add constraint df_temp_prikey
    default dbo.[fn_AutoIncrementPriKey_so]() for prikey
    go
    
    insert into temp_so (name) values ('ABC')
    go 5
    
    select * from temp_so

    Output:

    You can try to modify it based on your requirement.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 6:51 AM