Add column value using scalar valued function-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

It does not matter how you will execute your query. I guess, may be a problem with your function. So, I make a simple function & example here for you.

Suppose you have the following scalar function, where you've to add 1 with passed AgentId. So code will be as follows.

CREATE FUNCTION udf_GetInvoiceNumber
(
    @AgentId int
)
RETURNS int
AS
BEGIN       
    RETURN @AgentId 1

END
GO

And Select & Insert Query will remain as you posted and it's in below. And it returns multiple rows.

INSERT INTO dbo.tblabc (InvoiceNumber, AgentId)
SELECT [dbo].[udf_GetInvoiceNumber](cd.Id),cd.Id 
FROM dbo.tblxyz cd