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!!

does the function work standalone? And what version of SQL Server? — gbn, May 13 '13 at 13:47
The function does work standalone with hard-coded values and in a query window with declared variables, just not within a query and aliased columns. Function works on multiple version of sql, currently testing on sql2008 — Matt Holmen, May 13 '13 at 14:00

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

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

edited May 23 '17 at 12:03

Community

1
1

answered May 13 '13 at 14:04

gbn

422,506
82
585
676

That does seem to be the issue. Thanks. Now I have to see if I can change that on client machines or find another solution. – Matt Holmen May 13 '13 at 14:35

Using a sql function inside a subquery with parameters

1 Answers1