How to retrieve multiple items with all their attributes from an EAV data model?

Question

How could someone retrieve multiple products with all their attributes whose ids are passed as a comma separated list to a stored procedure?

Tables are designed in a simple EAV fashion like so:

tbl_products
  id
  title

tbl_product_attributes
  product_FK
  attribute_FK
  attribute_value

tbl_attributes
  id
  title

The following stored procedure do this for just one product:

CREATE PROCEDURE get_product
  @product_id NVARCHAR(MAX)
AS
  BEGIN
    -- Create a temp table to store values of get_product_attributes sp
    CREATE TABLE #temp ( attributes NVARCHAR(MAX) );

    -- Insert results of get_product_attributes sp into temp table
    INSERT INTO #temp (attributes)
    EXEC get_product_attributes @product_id;

    -- Select product with all its attributes
    SELECT id,
           title,
           ( SELECT attributes FROM #temp ) AS attributes
    FROM   tbl_products
    WHERE  id = @product_id;
  END;

But what if we want multiple product details given the product ids?

(get_product_attributes stored procedure return all attributes and their values of a specific product as json it uses dynamic sql to retrieve all attributes then return them as json; exactly like what dynamic sql part of this answer does: https://stackoverflow.com/a/13092910/5048383)

(also using sql server 2016, got access to STRING_SPLIT function and could easily transform passed ids to rows)

Answer 1

Generally speaking, looping is bad in SQL. For what you described, I can't imagine why it would warrant a loop. However, here is a set based method with simple joins that would eliminate everything but a single procedure.

declare @tbl_products table (
                            id int, 
                            title varchar(16))

insert into @tbl_products
values
(1,'prod1'),
(2,'prod2'),
(3,'prod3'),
(4,'prod4'),
(5,'prod5')

declare @tbl_attributes table (
                                id int,
                                title varchar(16))

insert into @tbl_attributes
values
(1,'attr1'),
(2,'attr2'),
(3,'attr3'),
(4,'attr4'),
(5,'attr5')


declare @tbl_product_attributes table (
                                        product_FK int,
                                        attribute_FK int,
                                        attribute_value varchar(64))

insert into @tbl_product_attributes
values
(1,5,'blah'),
(1,3,'blah blah'),
(2,1,'not sure'),
(2,3,'what should be here'),
(2,4,'but here is a'),
(3,5,'line of text'),
(3,4,'as a place holder')




declare @product_id nvarchar(4000)
set @product_id = '1,3'



if object_id ('tempdb..#staging') is not null
drop table #staging

select
    prod.id
    ,prod.title as ProductTitle
    ,'Attr' + cast(attr.id as char(8)) as AttributeID
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
into #staging
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
inner join
    string_split(@product_id,',') x
    on x.value = prod.id




DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(AttributeID)
FROM (SELECT DISTINCT AttributeID FROM #staging) AS AttributeID


--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'
    SELECT ProductTitle,  ' + @ColumnName + '
    FROM #staging
    PIVOT(min(AttributeTitle) 
          FOR AttributeID IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

So, you would just create the proc on the top part...

create proc yourProc(@product_id nvarchar(4000))
as

select
    prod.id
    ,prod.title as ProductTitle
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
where
    prod.id in (select * from string_split(@product_id,','))

NOTE: This can also be written more cleanly as a join

select
    prod.id
    ,prod.title as ProductTitle
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
inner join
    string_split(@product_id,',') x
    on x.value = prod.id