SQL Distinct keyword in assignment statement

Question

I have a query that works:

DECLARE @ProductID int
SET @ProductID = '1234'

SELECT DISTINCT TOP 12 a.ProductID
FROM A a
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID)
AND a.ProductID != @ProductID

It returns a list of 12 product numbers, all unique.

I need to store these results in a variable, comma separated, because that's what 3rd party stored procedure needs. So I have this:

 DECLARE @ProductID int
 DECLARE @relatedprods varchar(8000)
 SET @ProductID = '1234'
 SET @relatedprods = ''

 SELECT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
   FROM A a
   WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
   AND a.ProductID != @ProductID

 SELECT @relatedprods

Now, none of these are distinct, but it is returning 12 rows.

Now I add the 'distinct' back in, like in the first query:

 DECLARE @ProductID int
 DECLARE @relatedprods varchar(8000)
 SET @ProductID = '1234'
 SET @relatedprods = ''

 SELECT DISTINCT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
   FROM A a
   WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
   AND a.ProductID != @ProductID

 SELECT @relatedprods

Only one product is returned in the comma separated list! Does 'distinct' not work in assignment statements? What did I do wrong? Or is there a way to get around this?

Thanks in advance!

CONCLUSION:

I have no idea what causes this problem, though the guess proposed seems logical. I was able to solve this problem via sub-query, and am posting it so others can see the solution:

 DECLARE @ProductID int
 DECLARE @relatedprods varchar(8000)
 SET @ProductID = '1234'
 SET @relatedprods = ''

 SELECT @relatedprods = @relatedprods + CONVERT(VARCHAR(20), c.ProductID) + ',' 
    FROM (SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN 
      (SELECT b.CategoryID 
      FROM B b 
      WHERE B.ProductID = @ProductID) 
    AND a.ProductID != @ProductID ) c

 SET @relatedprods = SUBSTRING(@relatedprods, 0, LEN(@relatedprods))
 SELECT @relatedprods

Answer 1

Could you use a temp table?

DECLARE @relatedProdList TABLE (prod VARCHAR(20))
INSERT INTO @relatedProdList 
SELECT DISTINCT TOP 12 CONVERT(VARCHAR(20), a.ProductID)
FROM A a 
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID) 
AND a.ProductID != @ProductID 

SELECT @relatedprods = @relatedprods + prod + ', '
FROM @relatedProdList

Answer 2

Get the 12 records in a subquery:

declare
  @ProductID int,
  @relatedprods varchar(8000)

set @ProductID = '1234'
set @relatedprods = ''

select @relatedprods = @relatedprods + cast(ProductID as varchar) + ','
from (
  select distinct top 12 a.ProductId
  from A a
  inner join B b on b.CategoryID = a.CategoryID
  where  B.ProductID = @ProductID and a.ProductID != @ProductID
) x

Answer 3

This is just a wild guess, but I expect that doing assignment in SELECT fails with DISTINCT since it has to do a sort after generating the column value(s) to enforce the distinctness. I've seen similar behavior when adding a non-trivial ORDER BY clause (e.g., ordering by an expression).

If you have SQL Server 2005 or above, you can also use FOR XML PATH instead.

DECLARE @ProductID int
DECLARE @relatedprods varchar(8000)
SET @ProductID = 1234

SET @relatedprods = (SELECT DISTINCT TOP 12
    CONVERT(VARCHAR(20), a.ProductID) + ','
    FROM A a
    WHERE a.CategoryID IN (...)
    AND a.ProductID != @ProductID
    FOR XML PATH(''))

-- FOR XML PATH will add an extra comma at the end, so remove it.
IF LEN(@relatedProds) > 1
    SET @relatedProds = SUBSTRING(@relatedProds, 1, LEN(@relatedProds) - 1)

Since you're using a subquery instead of variable assignment syntax, those limitations don't apply.