3
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 ?

Java Main
  • 1,521
  • 14
  • 18
  • It's perfectly possible for it to return `Order Details` rows which have a `ProductID` that is less than 10. What you've got here is something that won't return `Order Details` rows for products 68 - 77. – Damien_The_Unbeliever May 29 '15 at 13:23
  • @Stephan what i want is firstly the UDF to return only productID >= 10 . i checked it works fine . But when combined with cross apply it returns some productID less than 10 . – Java Main May 29 '15 at 13:34
  • @Damien the code above should return same result as if you had an innder join for productID >= 10 . That is my goal – Java Main May 29 '15 at 13:35
  • @JavaMain I think Donal's code might be what you're looking for. – Stephan May 29 '15 at 13:36
  • Read you UDF again. It says "if you hand me an ID value of 1, return rows with an ID value of 11", which obviously works. You may just have your logic backwards (i.e. if you subtract 10 then it would say "if you hand me an ID value of 1, return rows with an ID value of -9", and those rows obviously don't exist) – Damien_The_Unbeliever May 29 '15 at 13:38

1 Answers1

3

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)
Donal
  • 31,121
  • 10
  • 63
  • 72