CODEWITHSUNDEEP
sqlsql-serversql-server-2005user-defined-functions
Jdunn5
Jdunn5

Reputation: 108

Calling Inline UDF with table alias.column name as a parameter

I have a Inline table valued Function that i want to pass a column from a select but will use a table alias.

For Example:

select top 1000 a.*, b.* from item a
LEFT JOIN itemList b on a.item_id = b.item_id
where a.item_id in (SELECT * FROM dbo.fn_GIVFUC('1234567', a.item_id))

Results in : Incorrect syntax near 'a'.

Thanks

Upvotes: 1

Views: 2228

Answers (1)

gbn
gbn

Reputation: 432271

You'd have to use CROSS APPLY like this

select top 1000  
   a.*, b.* 
from 
  item a
  CROSS APPLY
  dbo.fn_GIVFUC('1234567', a.item_id) c ON a.item_id = c.item_id
  LEFT JOIN 
  itemList b on a.item_id = b.item_id

This means you may get duplicates though, so this may work. I can't test

select top 1000  
   a.*, b.* 
from 
  item a
  LEFT JOIN 
  itemList b on a.item_id = b.item_id
WHERE
  EXISTS (
       SELECT *
       FROM dbo.fn_GIVFUC('1234567', a.item_id)
       -- may need this WHERE a.item_id = c.item_id
      )

Upvotes: 5

Related Questions