Concatenate with XML PATH

Question

I have the following SP :

CREATE PROCEDURE AdvancedSearch
    @MechanicID nvarchar(50),
    @done bit,
    @sDate datetime,
    @eDate datetime
 AS
 SELECT   VehicleData.*, Vehicle.*, C5.dbo.debkart.navn,
          C5.dbo.DEBKART.ADRESSE1, C5.dbo.debkart.adresse2,
          Mechanic.MechanicName
 FROM     (((Vehicle INNER JOIN VehicleData ON Vehicle.ID = VehicleData.IDRegistrationNumber)
          INNER JOIN C5.dbo.debkart ON Vehicle.KeyC5 = c5.dbo.debkart.LXbenummer
          INNER JOIN Mechanic_VehicleData ON VehicleData.ID = Mechanic_VehicleData.PK_VehicleData))
          INNER JOIN Mechanic ON Mechanic_VehicleData.PK_Mechanic = Mechanic.ID
 WHERE    (@MechanicID IS NULL OR Mechanic.ID = @MechanicID) AND
          (@done IS NULL OR VehicleData.Done = @done) AND
          ((@sDate IS NULL OR @eDate IS NULL) OR VehicleData.DATE BETWEEN @sDate AND    @eDate)
 GO

I would like to concatenate the MechanicName column across all rows with same VehicleData.ID so would like to do exactly as in this article here : Concatenate with XML Path but only on one filed being the MechanicName.

My problem is that i cannot figure out how to insert the sql code from the post inside my SP.

Can someone help me ?

UPDATE: I got something to work, i made this and it seems to return what i need but with a problem which i describe further down :

SELECT  VehicleData.*, Vehicle.*, C5.dbo.debkart.navn, C5.dbo.DEBKART.ADRESSE1, C5.dbo.debkart.adresse2,
    STUFF(
    (SELECT
    ',' + MechanicName
    FROM Mechanic m
    INNER JOIN Mechanic_VehicleData vm ON m.ID = vm.PK_Mechanic
    WHERE vm.PK_VehicleData = VehicleData.ID 
    FOR XML PATH('')), 1, 1, ''
    ) AS 'Mechanics'
FROM (((Vehicle INNER JOIN VehicleData ON Vehicle.ID = VehicleData.IDRegistrationNumber)           
INNER JOIN C5.dbo.debkart ON Vehicle.KeyC5 = c5.dbo.debkart.LXbenummer))
ORDER BY VehicleData.ID

So i got to the part where i need to add the WHERE clauses and now i do not know where to put those. I have a way of searching for a specific MechanicID, done flag and some dates. Where should i now put these ?

UPDATE 2

@thanks to marc for helping me out, i do still have an "small" issue though :)

Here is the final SP :

CREATE PROCEDURE AdvancedSearch 
@MechanicID nvarchar(50),
@done bit,
@sDate datetime,
@eDate datetime
AS 
SELECT  VehicleData.*, Vehicle.*, C5.dbo.debkart.navn, C5.dbo.DEBKART.ADRESSE1, C5.dbo.debkart.adresse2,
    STUFF(
    (SELECT
    ',' + MechanicName
    FROM Mechanic m
    INNER JOIN Mechanic_VehicleData vm ON m.ID = vm.PK_Mechanic
    WHERE vm.PK_VehicleData = VehicleData.ID 
    FOR XML PATH('')), 1, 1, ''
    ) AS 'Mechanics'
FROM (Vehicle
INNER JOIN VehicleData ON Vehicle.ID = VehicleData.IDRegistrationNumber         
INNER JOIN C5.dbo.debkart ON Vehicle.KeyC5 = c5.dbo.debkart.LXbenummer), Mechanic, Mechanic_VehicleData
WHERE VehicleData.ID = Mechanic_VehicleData.PK_VehicleData 
AND Mechanic.ID = Mechanic_VehicleData.PK_Mechanic 
AND (@MechanicID IS NULL OR Mechanic.ID = @MechanicID) 
AND (@done IS NULL OR VehicleData.Done = @done)
AND ((@sDate IS NULL OR @eDate IS NULL) OR VehicleData.Date BETWEEN @sDate AND @eDate)
GO

As said it works fine. The issue I have is that if I run EXECUTE AdvancedSearch null, null, null, null it takes 4 seconds for the query to finish. If I run the query out of the SP it takes NO TIME. I find that weird. Why does the SP take longer time?

Answer 1

Try to simplify down your example first, then figure out how to do this step in the simplified example - and then you can integrate this back into your overall query.

Try this - it works in my setup:

DECLARE @vehicle TABLE (VehID INT, VehName VARCHAR(50))
DECLARE @mechanic TABLE (MechanicID INT, MechanicName VARCHAR(50))
DECLARE @VehicleMechanic TABLE (VehID INT, MechanicID INT)

INSERT INTO @vehicle(VehID, VehName) VALUES(1, 'BMW 330i'),(2, 'Mini Cooper')

INSERT INTO @mechanic(MechanicID, MechanicName)
VALUES  (1, 'Joe'), (2, 'Jeff'), (3, 'Bob'), (4, 'Steve')

INSERT INTO @VehicleMechanic( VehID, MechanicID )
VALUES  ( 1, 1), (1, 2), (2, 1), (2, 3), (2, 4)

SELECT
    v.VehID, VehName, 
    STUFF(
    (SELECT
       ',' + MechanicName
     FROM @mechanic m 
     INNER JOIN @VehicleMechanic vm ON m.MechanicID = vm.MechanicID
     WHERE vm.VehID = v.VehID
     FOR XML PATH('')), 1, 1, ''
    ) AS 'Mechanics'
FROM @vehicle v

Gives me an output of:

VehID  VehName      Mechanics
  1    BMW 330i     Joe,Jeff
  2    Mini Cooper  Joe,Bob,Steve

Update: it's hard to verify anything, not having your database at hand - but try this:

SELECT  
    vd.*, v.*, d.navn, d.ADRESSE1, D.adresse2,
    STUFF(
      (SELECT
         ',' + MechanicName
       FROM dbo.Mechanic m
       INNER JOIN dbo.Mechanic_VehicleData vm ON m.ID = vm.PK_Mechanic
       WHERE vm.PK_VehicleData = v.ID 
       FOR XML PATH('')), 1, 1, ''
      ) AS 'Mechanics'
FROM dbo.Vehicle v 
INNER JOIN dbo.VehicleData vd ON v.ID = vd.IDRegistrationNumber
-- if you need the mechnic, add this JOIN    
INNER JOIN dbo.Vehicle_Mechanic vm ON vm.VehicleID = v.VehicleID  
INNER JOIN C5.dbo.debkart d ON v.KeyC5 = d.LXbenummer

WHERE                   -- add your WHERE clause here
   vd.DoneFlag = 1    
   AND vd.SomeDate = GETDATE()
   AND vm.MechanicID = 4711          -- check for your specific mechanic
ORDER BY 
   vd.ID

If it doesn't work - can you tell us if you get an error (and what it is), or whether you get the wrong output - what is wrong about it??

Any WHERE clauses should be added to the outer SELECT statement - the inner stuff is only for setting up the relationship between the VehicleData, Mechanic_VehicleData and Mechanic tables.