Stored Procedure that Concatenates Numbers

Question

I have a stored procedure that concatenates tracking numbers from multiple records into one.

This stored procedure below works fine

ALTER PROCEDURE [dbo].[Proc1]
   @HWOrderID NVARCHAR(20)
AS
   DECLARE @concatn NVARCHAR(MAX)

   SELECT 
      @concatn = COALESCE(@concatn + ' ','') +
                 COALESCE(IT.TrackingNum + ', ','')  
   FROM 
      HWEvent as hwe
   INNER JOIN 
      HWOrderID AS hwo ON hwo.HWEventpk = hwe.HWEventpk
   LEFT OUTER JOIN  
      InventoryTransaction IT ON hwo.HWEventpk = IT.HWEventpk
   WHERE
       hwo.HWOrderID = 21000
       AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'

   SELECT 
      @concatn AS 'FromSMHS'

   SET @concatn = null

My results:

FromSMHS
5560,  5561,  5562,  5563,  5564,  5565

But... now I need to add additional tracking numbers from a second table. When I join the additional tables I get duplicate tracking numbers.

ALTER PROCEDURE [dbo].[Proc2]
    @HWOrderID NVARCHAR(20)
AS
   DECLARE @concatn NVARCHAR(MAX)

   SELECT 
      @concatn = COALESCE(@concatn + ' ','') +
                 COALESCE(IT.TrackingNum + ', ','') +
                 COALESCE(fexd.TrackingNumber + ', ','')  
   FROM
      HWEvent as hwe
   INNER JOIN 
      HWOrderID AS hwo ON hwo.HWEventpk = hwe.HWEventpk
   LEFT OUTER JOIN  
      InventoryTransaction IT ON hwo.HWEventpk = IT.HWEventpk
   LEFT OUTER JOIN   
      FedexTrackingReference fexr ON hwe.HWEventID = fexr.HWEventID
   LEFT OUTER JOIN  
      FedExTrackingDetail fexd ON fexr.FedexTrackingDetailpk =  fexd.FedExTrackingDetailpk
   WHERE
      hwo.HWOrderID = 21000
      AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'

   SELECT @concatn AS 'FromSMHS'
   SET @concatn = null

My results:

FromSMHS
5560, 5561  5560, 8500,5560, 8501,  5560, 8502,  5562, 5561  5562, 8500,5562, 8501,  5562, 8502,  5563, 5561  5563, 8500,5563, 8501,  5563, 8502,  5564, 5561  5564, 8503, 8501,  5564, 8502,  5564, 5561  5564, 8503, 8501,  5564, 8502,  5565, 5561  5565, 8500,5565, 8501,  5565, 8502

My desired result should like this:

FromSMHS
5560, 5561, 5562, 5563, 5564,  5565, 8500, 8501, 8502,8503

You could just put the values into a temporary table first and do a distinct — Donal, Sep 11 '14 at 20:31

score 1 · Answer 1 · edited May 23 '17 at 12:07

1

I would union the results from the 2 tables and then I personally like using the FOR XML PATH trick to get a comma delimited string from multiple rows.

How to collate rows to a delimited string in SQL2008R2

edited May 23 '17 at 12:07

Community

1
1

answered Sep 11 '14 at 22:07

db_brad

903
6
22

M.Ali · Accepted Answer · 2014-09-11T22:16:18.127

Try this....

ALTER PROCEDURE [dbo].[Proc2]
@HWOrderID NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

SELECT DISTINCT
       STUFF((SELECT ', ' +  CAST(TrackingNum AS VARCHAR(100))
              FROM InventoryTransaction
              WHERE HWEventpk = IT.HWEventpk
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,'')

 + ' ' +STUFF((SELECT ', ' +  CAST(TrackingNumber AS VARCHAR(100))
              FROM FedExTrackingDetail
              WHERE fexr.FedexTrackingDetailpk = FedexTrackingDetailpk
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,'')  AS Result_List             

FROM  HWEvent as hwe
INNER JOIN  HWOrderID AS hwo            ON hwo.HWEventpk = hwe.HWEventpk  AND hwo.HWOrderID = @HWOrderID
LEFT  JOIN  InventoryTransaction IT     ON hwo.HWEventpk = IT.HWEventpk
                                       AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'
LEFT  JOIN  FedexTrackingReference fexr ON hwe.HWEventID = fexr.HWEventID
LEFT  JOIN  FedExTrackingDetail fexd    ON fexr.FedexTrackingDetailpk =  fexd.FedExTrackingDetailpk

END

Stored Procedure that Concatenates Numbers

2 Answers2