CROSS APPLY WITH UDF

Question

Create function  getbyID  ( @id int )
Returns table

as 
return( 
select * from Products where  

ProductID=@id+10)

The function above will retrieve all product records where the product ID is greater than 10.

When used with CROSS APPLY as shown below

select o.* from [Order Details] o 
CROSS APPLY getbyID(o.ProductID) P

I end up with some product ID less than 10, which is not possible.

The example uses a fetch from the NORTWIND database.

ORDER table table and PRODCUTS tables are linked to ProductID

Select* from getbyID (1)  gives result below

enter image description here

When the UDF is called (as above) the result shows some id of product <10

enter image description here

Can you see where the mistake is?

+3

Java Main May 29 '15 at 13:09

1 answer

Donal · Accepted Answer · 2015-05-29T13:23:15+0000

If you want your function to simply return products where ProductID is greater than 10, you must add this check to the where clause. For example:

Create function  getbyID  ( @id int )
Returns table
as 
return( 
select * from Products 
where  
ProductID=@id AND
ProductID > 10)