Query returns different result from C# then from SSMS-CodePudding

I have this weird problem that I cannot explain

When I run a certain query from a C# application, it returns a different result then when I run it in SSMS, but not when I use parameters

The command is this

select dbo.fnGetPlaceID('6025', null, null)

It returns -1 when run from the c# application, and returns 1489 when run from SSMS

But if I do this

declare @DealerCode varchar(30) = '6025'
declare @RegionCode varchar(50) = null
declare @MerkID int = null
select dbo.fnGetPlaceID(@DealerCode, @RegionCode, @MerkID)

Then it returns the correct value 1489 also from the c# application

So what is the difference ?

Additional code :

CREATE function dbo.fnGetPlaceID(@DealerCode varchar(30), @RegionCode varchar(50), @MerkID int) returns int as
begin
    declare @Result int = -1
    
    if @RegionCode = ''
       set @RegionCode = null
    
    select @Result = r.RelationID
    from   relation.tblRelation r
    where  r.dealercode = @DealerCode
    
    -- in the example of '6025' there is only one row, I checked this
    -- So the code below should not be executed    
    if @@ROWCOUNT <> 1
    begin
         select @Result = rel.RelationID
         from   relation.tblRelation rel
           left outer join dbo.tblRegionCodes r on rel.RegionCodeID = r.FordRegionCodeID
         where  rel.dealercode = @DealerCode
         and    (@RegionCode is null or r.RegionCode = @RegionCode)
         and    (@MerkID is null or rel.BrandID = @MerkID)
         
         if @@ROWCOUNT <> 1
            set @Result = -1
    end
    
    return @Result
end

And the code in c#

FillDataTable(myDataTable, "select dbo.fnGetPlaceID('6025', null, null)")

public void FillDataTable(DataTable table, string SqlText, int commandTimeOut = 300)
{
    if (_ConnectionString != null && _ConnectionString != "")
    {
        using (SqlConnection connection = new SqlConnection(_ConnectionString))
        {
            OpenConnection(connection, SqlText);

            using (SqlCommand command = new SqlCommand(SqlText, connection))
            {
                command.CommandType = CommandType.Text;
                command.CommandTimeout = commandTimeOut;

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    try
                    {
                        adapter.Fill(table);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }
    }
}

What I have tried

Then I tried this change in the sql function

alter function dbo.fnGetPlaceID(@DealerCode varchar(30), @RegionCode varchar(50), @MerkID int) returns int as
begin
    declare @Result int = -1
    
    if @RegionCode = ''
       set @RegionCode = null
    
    select @Result = r.RelationID
    from   relation.tblRelation r
    where  r.dealercode = @DealerCode
/*    
    -- in the example of '6025' there is only one row, I checked this
    -- So the code below should not be executed
    if @@ROWCOUNT <> 1
    begin
         select @Result = rel.RelationID
         from   relation.tblRelation rel
           left outer join dbo.tblRegionCodes r on rel.RegionCodeID = r.FordRegionCodeID
         where  rel.dealercode = @DealerCode
         and    (@RegionCode is null or r.RegionCode = @RegionCode)
         and    (@MerkID is null or rel.BrandID = @MerkID)
         
         if @@ROWCOUNT <> 1
            set @Result = -1
    end
*/    
    return @Result
end

And now it does works correct from the c# application.

So, for some reason when I call this function from a c# application it seems that @@ROWCOUNT is not equal to 1, and when I call this function from SSMS then @@ROWCOUNT is equal to 1.
I have not confirmed this, but that is my conclusion.
The question remains, why ?
And also why does it works different when I use parameters ?

I have fixed my problem by altering the function to

select @Count = count(1) 
from   relation.tblRelation r 
where  r.DealerCode = @DealerCode

and then use the variable @Count in stead of rowcount, but I would still like some explanation of what is happening here.

I also checked what would happen to the first query if more than one row is found.
Because I put the result of the query in a variable, that should fail if more than one row is found (An error in my function, I know, I fixed it by now)

declare @Result int = -1

select @Result = r.RelationID
from   relation.tblRelation r
where  r.dealercode is null

select @@ROWCOUNT

But in stead of throwing an exception what I expected, rowcount just had a value of 4512

EDIT

As suggested by @DanGuzman it works when I put TSQL_SCALAR_UDF_INLINING to OFF

select dbo.fnGetPlaceID('6025', null, null)
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))

EDIT 2

It also works without the hint if I do this ALTER DATABASE [myDataBase] SET COMPATIBILITY_LEVEL = 140; It was on 150

CodePudding user response：

There are a number of issues with scalar function inlining that have been fixed since the initial SQL Server 2019 RTM release. Install the latest cumulative update to get these fixes and others as well.

Without patching, work-arounds include:

disable inlining with query hint OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
disable inlining at the database level with USE YourDatabase;ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
Change the database compatibility level to 140 or lower

The latest SQL Server patch download links are listed on this page

Regarding different behavior from C# code and SSMS, this is due to different execution plans. SSMS queries specify the ARITHABORT ON session setting by default whereas C# does not set the option. Different session settings may yield different execution plans and make it difficult to troubleshoot. See Erland Sommarskog's article for details. I'll add that SSMS sets ARITHABORT only for backwards compatibility. It's on by default anyway in the SQL 2019 world because database compatibility level 80 (SQL 2000) is no longer supported. Consider unchecking the SET ARITHABORT option in SSMS under Tools-->Options-->Query Execution-->SQL Server--Advanced.