Así que imagina que tienes una mesa deProducts (ID int, Name nvarchar(200))y otras dos mesas,ProductsCategories (ProductID int, CategoryID int) yInvoiceProducts (InvoiceID int, ProductID int).

Necesito escribir una consulta para producir un conjunto de productos que coincidan con un conjunto dado de identificadores de factura y de categoría, de modo que la lista de productos coincida con todas las categorías especificadas y todas las facturas especificadas, sin recurrir al SQL dinámico. Imagine que necesito encontrar una lista de productos que están en las categorías 1 y 2 y en las facturas 3 y 4.

Como principio, he escrito un procedimiento almacenado que acepta los identificadores de categoría y los identificadores de factura como cadenas, y los analiza en tablas:

 CREATE PROCEDURE dbo.SearchProducts (@categories varchar(max), @invoices varchar(max))
 AS BEGIN
      with catids as (select cast([value] as int) from dbo.split(@categories, ' ')),
           invoiceids as (select cast([value] as int) from dbo.split(@invoices, ' '))
           select * from products --- insert awesomeness here
 END

Las diferentes soluciones que se me ocurren se ven horribles y funcionan peor. Lo mejor que he encontrado es generar una vista compuesta por uniones izquierdas de todos los criterios, pero eso parece muy costoso y no resuelve el problema de hacer coincidir todas las diferentes claves especificadas.

Actualizar: Este es un ejemplo de consulta que escribí que produce los resultados esperados. ¿Me estoy perdiendo alguna oportunidad de optimización? ¿Como las operaciones mágicas de matriz de unicornio de ninjas?

with catids as (select distinct cast([value] ,as int) [value] from dbo.split(@categories, ' ')),
  invoiceids as (select distinct cast([value] as int) [value] from dbo.split(@invoices, ' '))

  select pc.ProductID from ProductsCategories pc (nolock)
    inner join catids c on c.value = pc.CategoryID 
    group by pc.ProductID 
    having COUNT(*) = (select COUNT(*) from catids)  
  intersect
  select ip.ProductID from InvoiceProducts ip (nolock)
    inner join invoiceids i on i.value = ip.InvoiceID 
    group by ip.ProductID 
    having COUNT(*) = (select COUNT(*) from invoiceids)