0

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

2
  • does the function work standalone? And what version of SQL Server? Commented May 13, 2013 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 Commented May 13, 2013 at 14:00

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.