Add column value using scalar valued function

user_1083030 0
Viewed 173

I'm copying data from one table to another and during the copy i'm adding a autogenerated number.

The below query is a job that runs every day to add invoices for a specific day into the table.

insert into dbo.tblabc (InvoiceNumber, AgentId)
select [dbo].[udf_GetInvoiceNumber](AgentId),AgentId from dbo.tblxyz

The function [dbo].[udf_GetInvoiceNumber](cd.AgentId) returns the InvoiceNumber based on the Agent already available.

//Function

ALTER function [dbo].[udf_GetInvoiceNumber](@AgentId int)
returns varchar(50)
begin
 DECLARE @JC varchar(50) = 0
    if exists(SELECT count(agentid) from dbo.tblabc where AgentId = @AgentId and year(CreatedDate) = year(getdate()) group by agentid)
    begin
        Set @JC = (SELECT count(agentid) from dbo.tblabc where AgentId = @AgentId and year(CreatedDate) = year(getdate()) group by agentid)
    end 
    Set @JC = (Select concat('ALD/SHJ/',year(getdate()),'/INV/000', @JC + 1))
return @JC
end

Now, this doesn't work and updates only one row with InvoiceNumber when multiple rows are added at the same time.

Any other way to achieve this?

tsql sql-server sql
1 Answers

the challenge you are facing here is because of the function. the function always checks for the values which are already there in the database and generates the Invoice number for you. so when You have 2 or more rows to be inserted from the source, the destination still holds the same number of rows, so it'll return the same ID for all the rows. so to overcome this, try generating the invoice number outside the FUnctions. maybe try with the below approach

;WITH CTE
AS
(
    SELECT
        AgentId,
        YearCount = COUNT(1)
        FROM dbo.YourTable
            WHERE YEAR(CreatedDate) = YEAR(GETDATE())
        GROUP BY 
            AgentId
)
INSERT INTO dbo.DestinationTable
(
    InvoiceNumber, 
    AgentId
)
SELECT
    InvoiceNumber = concat('ALD/SHJ/',year(GETDATE()),'/INV/000', ROW_NUMBER() OVER(PARTITION BY AgentId ORDER BY AgentId )+ISNULL(CTE.YearCount,0)+1), --Generate ID without a function which will be much faster also
    AgentId = SRC.AgentId
    FROM SourceTable SRC
        LEFT JOIN CTE
            ON SRC.AgentId = cte.AgentId
user_51061540
Related