Why do Sql Server In operator behaves differently and return nothing?

  • I am using sql server 2017.

    I am trying to use the in operator, but it behaves me so differently.

    What I am trying to do is

    I created a cte and split the comma separated string into array and use this cte in my end query through in operator.

    But In operator should return all those results even if any of the value exists within the in operator.

    But unfortunately, It is not returning the values Yet If all values exists then It return the result.

    See my query please

    declare @franchiseId int = 16367;
    declare @vehicleCategoryId int = '1';
    declare @vehicleCategoryIds nvarchar(100) = '1,8';

    -- Select * FROM Franchise Where name like '%catford%'

    SET NOCOUNT ON;

    DECLARE @p1 Int = 1;
    DECLARE @p2 Int = 1;
    DECLARE @p3 Int = 1;
    DECLARE @p4 Int = 1;
    DECLARE @p5 Decimal(5,4) = 0;
    DECLARE @p6 Decimal(5,4) = 0;
    DECLARE @p7 Int = 0;

    ;WITH CTEVehCatId
    AS (SELECT value
    FROM STRING_SPLIT(@vehicleCategoryIds, ',')
    )
    Select tblDer.Id , tblDer.FuelTypeText, tblDer.TransmissionText, tblDer.Derivative, tblDer.FleetPrice, tblDer.GroupName,
    tblDer.GroupId, tblDer.SWRate,tblDer.LWRate,tblDer.Tariff,tblDer.PassengerSeats, tblDer.GroupIcon, tblDer.Category,
    tblDer.OrderNo, tblDer.IsEnquiry, tblDer.IsBooking, tblDer.IsGhost
    FROM
    (Select f.Id Id, (select top 1 FuelType FROM FleetFuelTypes (nolock) where Fleet_Id = f.Id ) FuelTypeText,
    (select top 1 Transmission FROM FleetTransmissions (nolock) where Fleet_Id = f.Id ) TransmissionText,
    f.Derivative Derivative,b.BandPrice FleetPrice, g.Name GroupName, g.Id GroupId,
    isnull(t.SWRate,0) SWRate,isnull(t.LWRate,0) LWRate,t.Id Tariff, isnull(f.PassengerSeats,0) PassengerSeats,g.Icon GroupIcon,
    c.Name Category, g.OrderNo, [g].IsEnquiry, [g].IsBooking, [g].IsGhost,
    ROW_NUMBER() over (partition by f.Id order by g.OrderNo desc) rowNum
    FROM [Tariff] AS [t] with (nolock)
    INNER JOIN [VehicleCategories] AS [c] with (nolock) ON [t].[FleetCategory_Id] = [c].[Id]
    INNER JOIN [FranchiseFrontendVehicleCategory] ffvc with (nolock) ON ffvc.[VehicleCategoryId] = [c].Id
    INNER JOIN
    (
    Select tblVeh.* FROM
    (Select [vg].*, fvg.FranchiseId , CAST(isnull(fvg.[IsEnquiry],0) as Bit) IsEnquiry, CAST(isnull(fvg.[IsBooking],0) as Bit) IsBooking,
    CAST(isnull(fvg.[IsGhost],0) as Bit) IsGhost,
    ROW_NUMBER() over (partition by vg.Id order by vg.Id) RowNo
    FROM [VehicleGroups] (nolock) AS [vg]
    left join [FranchiseVehicleGroups] fvg (nolock) on [vg].Id = fvg.VehicleGroupId --and fvg.FranchiseId = @franchiseId
    Where [vg].StatusId = 1
    ) tblVeh Where tblVeh.RowNo = 1 and tblVeh.FranchiseId = @franchiseId
    ) AS [g] ON [t].[Group_Id] = [g].[Id]
    -- [VehicleGroups] (nolock) AS [g] ON [t].[Group_Id] = [g].[Id]
    INNER JOIN [BandRates] (nolock) AS ON [t].[Id] = .[Tariff_Id]
    INNER JOIN [Fleets] (nolock) AS [f] ON [t].[Id] = [f].[Tariff_Id]
    LEFT OUTER JOIN [FleetDisposal] (nolock) AS [d] ON [f].[Id] = [d].[Fleet_Id]
    WHERE ffvc.[FranchiseId] = @franchiseId AND isnull(ffvc.IsBooking,0) = 1
    AND ([t].[Franchise_Id] = @franchiseId) AND ([t].[FleetCategory_Id] in (Select value from CTEVehCatId)) AND ([f].[CoreFleet] = @p2) AND ([f].[StatusId] = @p3)
    AND (((([d].[Fleet_Id]) IS NULL) AND ([f].[WebEnabled] = @p4) AND (NOT ([f].[GhostVehicle] = 1))) OR ([f].[GhostVehicle] = 1))
    -- AND [g].ShowOnWeb = 1
    ) tblDer Where tblDer.rowNum = 1 and (tblDer.IsEnquiry = 1 OR tblDer.IsBooking = 1)
    ORDER BY [tblDer].[OrderNo];

    Please focus on this part:

    ([t].[FleetCategory_Id] in (Select value from CTEVehCatId)) 
    @vehicleCategoryIds have '1,8';

    See the plan at here

    https://www.brentozar.com/pastetheplan/?id=B11apdVvo

    If I use this directly equal operator then it works

    [t].[FleetCategory_Id] = @vehicleCategoryId

    This works perfectly

    but why not with this expression

    ([t].[FleetCategory_Id] in (Select value from CTEVehCatId))

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Sorry  not able to replicate the issue.  The OP code relies on implicit conversion between INT and NVARCHAR which generally works but is an error prone (and awful for the next programmer to deal with) habit imo

    declare @vehicleCategoryId int = '1';
    declare @vehicleCategoryIds nvarchar(100) = '1,8';

    ;WITH CTEVehCatId
    AS (SELECT value
    FROM STRING_SPLIT(@vehicleCategoryIds, ','))
    select *
    from (values (1), (2), (8)) v(num)
    where v.num in(select value from CTEVehCatId);

    ;WITH CTEVehCatId
    AS (SELECT value
    FROM STRING_SPLIT(@vehicleCategoryIds, ','))
    select *
    from (values (1), (2), (8)) v(num)
    where v.num =@vehicleCategoryId;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thank you everyonw.

    my issue has been resolved.

     

    by adding this

    and fvg.FranchiseId = @franchiseId,

    ,

    it works . may be there is some data issue in this table [FranchiseVehicleGroups].

    • This reply was modified 2 months ago by  mehmood.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply