Coalesce for fetching ids

Question

Here is the coalesce function I want to use to fetch all the contract ids separated by comma with contract title in where clause.

declare @tempContractID int
 SELECT @tempContractID = COALESCE(@tempContractID,'') + ContractID + ','
   FROM Icn_Contracts where title like '%t'

    select @tempContractID as allcontrcats

But I get this error:

Conversion failed when converting the varchar value ',' to data type int.

When I use coalesce for fetching contract names then it does not show any error.

Why are you declaring an int but then building a comma-separated string? — Aaron Bertrand, Dec 17 '13 at 04:40

Aaron Bertrand · Accepted Answer · 2022-02-04T04:53:42.107

declare @tempContractID VARCHAR(MAX); -- you can't build a string as an int

 SELECT @tempContractID = COALESCE(@tempContractID,'') 
   + CONVERT(VARCHAR(11),ContractID) -- needs to be a string, as error says
   + ',' FROM dbo.Icn_Contracts -- please always use schema prefix
   where title like '%t';

    select @tempContractID as allcontrcats;

Though I prefer this method because, if you want to rely on the order of the output, you can (if you add an ORDER BY to the above query, the resulting order is still undefined).

SELECT @tempContractID = STUFF((SELECT ',' 
  + CONVERT(VARCHAR(11), ContractID)
  FROM dbo.Icn_Contracts
  WHERE title LIKE '%t'
  ORDER BY ContractID -- well-defined
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,'');

Coalesce for fetching ids

1 Answers1