0

The following function is causing me issues. It always falls into the IF @@ROWCOUNT = 0 even when the @Result has a value in it. I have ran these statements in isolation outside the function to confirm that there is data and the rowcount is actually 1. Yet somehow, every time the @@ROWCOUNT = 0 in the IF.

My collegues do not get this problem, only me. They are using SQL Server 2017 and I am using 2019. Also I wonder if it could be related to the speed of my machine vs others, ie. the number of threads etc.

ALTER FUNCTION [fnc_SomeFuncion]
(
    @Param1 varchar(100),
    @Param2 nchar(10)

)
RETURNS INT
AS
BEGIN

    DECLARE @Result bigint

    SELECT @Result = v.Id
    FROM vSomeView v WITH(NOLOCK)
    WHERE v.X = @Param1 AND v.Y = 1 AND v.Z = @Param2

    -- PRINT @Result -- prints 1

    IF @@ROWCOUNT = 0 -- should be 1 but is 0
        BEGIN
            ... -- always enters here
        END

    RETURN @Result -- this is a valid id but never gets returned
END
sprocket12
  • 5,368
  • 18
  • 64
  • 133
  • 3
    https://stackoverflow.com/questions/58952917/unexpected-rowcount-behavior-inside-an-udf-in-ms-sql-2019 – lptr Apr 25 '20 at 15:06
  • Ahh yes, hadn't noticed the OP said 2019. Clearly this is the Scalar inlining at work. if you don't want to disable inlining across the database, then use `WITH INLINE = OFF` in the function's declartion (goes prior to the `AS BEGIN`). – Thom A Apr 25 '20 at 15:09
  • 1
    And the relevant answer on that question is a pointer to update to SQL Server 2019 CU4 or later, which disables inlining if you use @@rowcount. https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019 – David Browne - Microsoft Apr 25 '20 at 15:55

0 Answers0