Problem when using @@rowcount on function in SQL Server 2019-CodePudding

I am using @@rowcount in my functions like this:

ALTER FUNCTION [dbo].[GetUserNameFamily] 
    (@UsrID INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Name NVARCHAR(MAX)
    DECLARE @Family NVARCHAR (MAX)
    DECLARE @cou INT
    
    SELECT @Name = ut.Fname, @Family = ut.Lname
    FROM User_tbl ut 
    WHERE ut.UserID = @UsrID

    IF @@ROWCOUNT = 0
        RETURN 'row 0'

    IF @Name IS NULL
        SET @Name = ''

    IF @Family IS NULL  
        SET @Family = ''

    RETURN @Name   ' '   @Family    
END

When I use this function in a query like that:

declare @ID int=3118

select * 
from Files_tbl 
where RefID = @ID    -- query rows affected is 0
    
select 
    dbo.GetUserNameFamily(TicketResponse_tbl.CreateByUserID) as CreateByFullName
from 
    TicketResponse_tbl
where 
    TicketResponse_tbl.TicketID = @ID

My result is:

After removing where in "select Files_tbl" query and changed this query rows affected from 0 to n.

declare @ID int = 3118

select * 
from Files_tbl 
-- where RefID = @ID  -- query rows affected is not 0
    
select 
    dbo.GetUserNameFamily(TicketResponse_tbl.CreateByUserID) as CreateByFullName
from 
    TicketResponse_tbl
where 
    TicketResponse_tbl.TicketID = @ID

My function result changes to :

This problem occurred after upgrading the database compatibility level to SQL Server 2019

CodePudding user response：

As mentioned by others, there was a bug in the new (2019) feature called Scalar UDF Inlining that involved side-affecting functions such as @@ROWCOUNT. Updating to the latest build of SQL Server (which you should do anyway) would have fixed this.

Be that as it may, to continue using Inlining you can avoid @@ROWCOUNT by simplifying your function like this

CREATE OR ALTER FUNCTION [dbo].[GetUserNameFamily] 
    (@UsrID INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ISNULL((
        SELECT CONCAT(ut.Fname, ' ', ut.Lname)
        FROM User_tbl ut 
        WHERE ut.UserID = @UsrID
    ), 'row 0');
END

But I would advise you to just transform this into an inline Table Valued Function, which will always be inlined:

CREATE OR ALTER FUNCTION [dbo].[GetUserNameFamily] 
    (@UsrID INT)
RETURNS TABLE
AS RETURN

SELECT
  ISNULL((
        SELECT CONCAT(ut.Fname, ' ', ut.Lname)
        FROM User_tbl ut 
        WHERE ut.UserID = @UsrID
    ), 'row 0') AS UserName;

You use it like this

SELECT n.UserName
FROM YourTable t
CROSS APPLY dbo.GetUserNameFamily(t.Id) n;