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

Question

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

gbn · Accepted Answer · 2012-02-20 16:34:37Z

5

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
      )

answered Feb 20, 2012 at 16:34

gbn

434k84 gold badges602 silver badges690 bronze badges

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

6 Comments

Jdunn5 Over a year ago

Thanks for the quick reply but i still get the same error : "Incorrect syntax near 'a'."

Jdunn5 Over a year ago

It really seems that sql does not like me passing the a.column name to the function. It is a INLINE Table Function that returns a table and I am expecting multiple results, that is why my initial sql used in

gbn Over a year ago

In which case, check the DB compatibility mode. It need to be 90 (or higher for SQL Server 2008+) to allow this. It won't work in "80"

Jdunn5 Over a year ago

This is currently being run on sql 2005 server, so would that setting be the same?

Aaron Bertrand Over a year ago

Not necessarily, no. Did you check it? See stackoverflow.com/questions/9318684/…

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related