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
FINAL UPDATE
I have installed the CU15 today, and tested again with compatibility level set to 150 and now it works as expected also from c#. So this solution is now confirmed.
