Using a sql function inside a subquery with parameters

Question

I have the following query that works:

SELECT 
  STUFF
  (
    (
      SELECT
        ', ' + RTRIM(LTRIM(ulastname))
      FROM 
        ROOM r LEFT JOIN person p ON p.hmy = r.hmyperson
      WHERE 1=1 
        AND r.hmytenant = t.hmyperson
      FOR XML PATH('')
    )
  , 1 
  , 2 
  , ''
  )
FROM unit u
  LEFT JOIN tenant t ON u.hproperty = t.hproperty
WHERE 1=1
  AND t.hmyperson = 71
  AND u.hmy = 19

However, what I need to do is to use a function in the innermost select statement to return the proper values. I have written the query as follows:

SELECT 
  STUFF
  (
    (
      SELECT 
        ', ' + RTRIM(LTRIM(fullname))
      FROM 
        SMS_All_Occupants(u.hmy, 'Y', t.hmyperson) occ
      WHERE 1=1 
        AND TYPE = 'T' 
        AND LESSEE = 'Y'
      ORDER BY 
        occ.relation asc
      FOR XML PATH('')
    )
  , 1 
  , 2 
  , ''
  )
FROM unit u
  LEFT JOIN tenant t ON u.hproperty = t.hproperty
WHERE 1=1
  AND t.hmyperson = 71
  AND u.hmy = 19

This query always fails with "Incorrect syntax near '.'", which seems to have to do with the u.hmy and t.hmyperson being passed into the function. If I put an actual value in for each of these paramaters it works as it should.

Can someone tell me what I am doing wrong? I have seen references to using a cross join but I cannot figure out how to make the work.

Thanks!!

Answer 1

Is the DB Compatibility level still "80"? If so, you cannot use columns as UDF parameters

More here: "Incorrect syntax" using a table-valued function in SELECT clause of T-SQL query