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

Question

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

What is your exact version of SQL Server? I would suggest you haven't updated your instance and have inlining issues. — Thom A
– Thom A ♦, Commented May 9, 2022 at 17:35
That's the release, I want the exact version. What is the output of PRINT @@VERSION? — Thom A
– Thom A ♦, Commented May 9, 2022 at 17:37
when i change compatibility and downgrade that. @@rowcount work correctly — ehsanHB
– ehsanHB, Commented May 9, 2022 at 17:38
Update your instance; you are almost 3 years behind in updates, and 16 CUs behind. This was a bug that was fixed ages ago. @@ROWCOUNT was omitted from inlining in CU2; that was released on 2020-02-13. Your instance doesn't even have the GDR updates, so has known security flaws. — Thom A
– Thom A ♦, Commented May 9, 2022 at 17:40

Charlieface · Accepted Answer · 2022-05-09 22:27:17Z

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related