ALTER PROCEDURE [dbo].[sp_invArticulos]
(
    @tipo          INT           = 0,
    @codbus        VARCHAR(200)  = '',
    @codalt        VARCHAR(200)  = '',
    @nomart        VARCHAR(200)  = '',
    @nomfam        VARCHAR(200)  = '',
    @nomsub        VARCHAR(200)  = '',
    @codfam        VARCHAR(200)  = '',
    @codsub        VARCHAR(200)  = '',
    @codbod        INT           = 0,
    @exacta        INT           = 0,
    @compuestos    INT           = 0,
    @facturables   INT           = 0,
    @balanza       INT           = 0,
    @origen        INT           = 0,
    @sistema       VARCHAR(10)   = '',
    @activo        INT           = 0,
    @nomart2       VARCHAR(200)  = ''
)
AS
BEGIN
    SET NOCOUNT ON;

  
    DECLARE @nomartBusqueda VARCHAR(200) = LTRIM(RTRIM(ISNULL(@nomart, '')));

   
    IF @exacta = 0
    BEGIN
        SET @codbus = '%' + @codbus + '%';
        SET @codalt = '%' + @codalt + '%';
        SET @nomfam = '%' + @nomfam + '%';
        SET @nomsub = '%' + @nomsub + '%';
        -- @nomart / @nomart2 quedan tal cual para búsqueda por palabras
    END

    IF @exacta = 1
    BEGIN
        SET @codbus = @codbus + '%';
        SET @codalt = @codalt + '%';
        SET @nomfam = @nomfam + '%';
        SET @nomsub = @nomsub + '%';
    END

    ;WITH PalabrasBusqueda AS
    (
        SELECT palabra
        FROM dbo.fn_SplitPalabras(@nomartBusqueda)
    )
    SELECT TOP (500)
           ART.codbus,
           ART.codalt,
           ART.nomart,
           invFamilia.nomfam,
           invSubFamilia.nomsub,
           ART.codart,
           ART.codfam,
           ART.codsub,
           CASE WHEN invFamilia.controlstock = 1 THEN 'SI' ELSE 'NO' END AS controlstock,
           (SELECT COALESCE(SUM(saltot), 0.00)
              FROM invStock
             WHERE codbus = ART.codbus
               AND invStock.codbod = @codbod) AS saltot,
           (SELECT COALESCE(SUM(saltot), 0.00)
              FROM invStock
             WHERE codbus = ART.codbus)        AS stogen,
           invUnidadMedida.abruni,
           exento,                    
           ART.coduni,
           ART.nomart2,
           ART.medalt,
           CASE
               WHEN ART.sistema = 0 OR ART.sistema IS NULL THEN 'N/A'
               WHEN ART.sistema = 1 THEN 'S'
               WHEN ART.sistema = 2 THEN 'L'
               WHEN ART.sistema = 3 THEN 'S/L'
           END AS sistema
    FROM invArticulos AS ART
    INNER JOIN invSubFamilia
        ON ART.codsub = invSubFamilia.codsub
    INNER JOIN invUnidadMedida
        ON ART.coduni = invUnidadMedida.coduni
    INNER JOIN invFamilia
        ON ART.codfam = invFamilia.codfam
       AND invSubFamilia.codfam = invFamilia.codfam
    WHERE
        (@codbus = ''  OR ART.codbus LIKE @codbus)
        AND (@codalt = '' OR ART.codalt LIKE @codalt)
        AND (@nomfam = '' OR invFamilia.nomfam LIKE @nomfam)
        AND (@nomsub = '' OR invSubFamilia.nomsub LIKE @nomsub)
        AND (@codfam = '' OR ART.codfam = @codfam)
        AND (@codsub = '' OR ART.codsub = @codsub)
        AND (@compuestos = 0 OR (@compuestos = @compuestos))
        AND (@facturables = 0 OR (invFamilia.facturable = @facturables))
        AND ART.activo = 1

        AND (
             @nomartBusqueda = ''
             OR NOT EXISTS (
                    SELECT 1
                    FROM PalabrasBusqueda pb
                    WHERE ART.nomart  NOT LIKE '%' + pb.palabra + '%'
                      AND ART.nomart2 NOT LIKE '%' + pb.palabra + '%'
                 )
        );
END
GO