Search code examples
sqlsql-servert-sqlinner-joinudf

CROSS APPLY WITH UDF

Create function  getbyID  ( @id int )
Returns table

as 
return( 
select * from Products where  

ProductID=@id+10)

Function above retruns all records of the Products where the product Id is grater than 10 .

When used with CROSS APPLY as below

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

i get in the result some productID less than 10 which is not possible .

The example uses NORTWIND database sample available everywhere .

ORDER DETAILS table and PRODCUTS tables are linked by the ProductID

Select* from getbyID (1)  gives result below

enter image description here

When the UDF is called (as above) result shows some productID < 10

enter image description here

Can you see where is the error ?

Solution

  • If you want your function to just return products where the ProductID is greater than 10, you should 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)