0

I'm trying to make a stored procedure about a filtering search of certain tables. I'm passing the list values by parameters and adding it to temp tables.

My logic is failing and I would like to clarify if I'm in a good path. There are three filters "Services","Equipment" and "Zones" The user can select one or more filters and each filter can have one or more items.

My doubts start by how can I define if only one or two filters is being applied. How I can be certain that the condition is being applied correctly?

This is my query:

ALTER PROCEDURE [dbo].[SearchFilterProvider]
(@SearchService nvarchar(max)=NULL,@SearchEquipment nvarchar(max)=NULL, @SearchZone nvarchar(max)=NULL)
AS
BEGIN
    BEGIN TRAN
        BEGIN TRY
            CREATE TABLE #Services (TipoServicio nvarchar(35));
            INSERT INTO #Services (TipoServicio)
                SELECT VALUE FROM string_split(@SearchService,'|');
            CREATE TABLE #Equipments (TipoEquipo nvarchar(35));
            INSERT INTO #Equipments (TipoEquipo)
                SELECT VALUE FROM string_split(@SearchEquipment,'|');
                CREATE TABLE #Zones (NombreZona nvarchar(35));
            INSERT INTO #Zones (NombreZona)
                SELECT VALUE FROM string_split(@SearchZone,'|');
            
            SELECT P.RazonSocial as 'Razon Social Proveedor', P.TipoProveedor as Tipo,
            (
                SELECT STRING_AGG(Z.NombreZona,';')
                FROM Rel_ProvZona pZ
                INNER JOIN Zona Z on pZ.idZona= Z.idZona
                WHERE pZ.idProveedor=P.idProveedor
            )AS Zonas,
            P.Estatus,P.Ciudad,P.Estado,P.Pais,P.Comentarios,U.Nombre+' ' +U.Apellido AS Usuario
            FROM Proveedor P
            INNER JOIN Usuario U ON P.Despachador=U.idUsuario
            INNER JOIN Rel_ProvServicio pS ON P.idProveedor= pS.idProveedor
            INNER JOIN Servicio S ON pS.idServicio=S.idServicio
            INNER JOIN Rel_ProvEquipo pE ON P.idProveedor= pE.idProveedor
            INNER JOIN Equipo E ON pE.idEquipo=E.idEquipo
            INNER JOIN Rel_ProvZona pZ ON P.idProveedor= pZ.idProveedor
            INNER JOIN Zona Z ON pZ.idZona=Z.idZona
            WHERE  S.TipoServicio IN (SELECT TipoServicio FROM #Services) OR  Z.NombreZona IN (SELECT NombreZona FROM #Zones) OR E.TipoEquipo IN (SELECT TipoEquipo FROM #Equipments)
          GROUP BY P.idProveedor,P.RazonSocial,P.TipoProveedor, P.Estatus,P.Ciudad,P.Estado,P.Pais,P.Comentarios,(U.Nombre+' '+U.Apellido)
            COMMIT TRAN
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT>0
                BEGIN
                    ROLLBACK TRAN
                END
        END CATCH
END

Is not only check if parameters are NULL is to check if the conditions are well applied in the where clause, because if I make a consult:

EXEC SearchFilterProvider NULL, NULL 'Norte|Bajio'
+-----+---------------+----+
| RazonSocial | Zonas      | 
+-----+--------------------+ 
| R1          | Bajio      |
| R2          |Norte;Bajio |
+-----+---------------+----+

It no longer appears with duplicates and by the consult only R2 must appear because it match with the condition

itsFerxis
  • 31
  • 6
  • You have a lot of joins there. Perhaps the joins are introducing the duplicates – Nick.Mc Aug 10 '23 at 22:49
  • You should be able to simplify your logic quite a bit since if the parameter is NULL, your temp table will be empty. i.e. just `E.TipoEquipo IN (SELECT TipoEquipo FROM #Equipments)` – Nick.Mc Aug 10 '23 at 22:59
  • The joins make the union of the relation of three tables table A, B and AB. I already apply that where condition but it throws duplicate and registers that dont match with the parameters I sent. Example: The condition from above – itsFerxis Aug 11 '23 at 14:58
  • I made some changes adding a group by to ensures only a single row is returned for each unique combination of columns – itsFerxis Aug 11 '23 at 15:20
  • 1
    Just a word: if this is a join issue then you are making the SQL Server do twice as much work as it needs: firstto join redundant rows, second to use group by to take them out again – Nick.Mc Aug 14 '23 at 03:32
  • @Nick.McDermaid yeah its no longer a join issue i made the changes like you commented by grouping by them. – itsFerxis Aug 14 '23 at 15:32

0 Answers0