Cross apply scope is not visible?

Question

Currently I have a udf which returns a table. it returns 3 rows . Each row return the parameter's value *10

Something like (pseudo):

ALTER FUNCTION [dbo].[myUdf]
(   
    @num int
)
RETURNS  @myTable TABLE (h int )     

AS
begin
    insert into @myTable 
    SELECT h=@num * 10 UNION
    SELECT h=@num * 20 UNION
    SELECT h=@num * 30
    return @myTable
end

Ok.

Now , in my code I do something like :

select  .... , 
has20 = CASE WHEN EXISTS (SELECT 1 FROM  dbo.myUdf(A.ID) WHERE h=20) 
             THEN 0 ELSE 1 end
,
has30 = CASE WHEN EXISTS (SELECT 1 FROM  dbo.myUdf(A.ID) WHERE h=30)
             THEN 0 ELSE 1 end
...
from A join B...on ...
Where x .. or ..y ... or Exists (select 1 from dbo.myUdf(A.ID) )

Please notice multiple usages :

Ok. So I was told to use Cross apply and so I did :

So let's take a real simple example :

I have this 3 rows of data :

DECLARE @t TABLE(myNum INT)
INSERT @t
VALUES  (1), (2), (3)

so let's use cross apply :

SELECT  has20  = CASE WHEN  EXISTS( SELECT h FROM myCrossApply WHERE mynum=20 ) 
             THEN '1' ELSE '0' END   , 
        has30  = CASE WHEN  EXISTS( SELECT h FROM myCrossApply WHERE mynum=30 ) 
             THEN '1' ELSE '0' END  
FROM   @t tmp
       CROSS APPLY (
         -- notice  ! in reality there is a udf Table here , I jsut made a simple conversion insted so you can test it.
         SELECT h = tmp.myNum * 10 UNION SELECT h = tmp.myNum * 20 UNION SELECT h = tmp.myNum * 30
        ) myCrossApply

But there is 2 errors here Which I don't know how to solve :

Question #1

• It doesn't recognize myCrossApply in the EXISTS clause :

How can I solve this ?

Question #2

• Also , the rows are mutipled becuase of the cross apply

For example ( let's remove the unknown exists clause to show the second problem) :

SELECT  dummy =  tmp.myNum , myCrossApply.h
         /*...*/ 

FROM   @t tmp
       CROSS APPLY (
         SELECT h = tmp.myNum * 10 UNION SELECT h = tmp.myNum * 20 UNION SELECT h = tmp.myNum * 30
        ) myCrossApply

How can I solve this ?

I just don't want the UDF to be recalculated each time , so they suggested to use cross apply.

Answer 1

myCrossApply is not a real table, it is a reference to a alias for a subset of data. You would not be able to refer to tmp as a table either.

Here is some code that should use the same logic as your script:

declare @t table(mynum int)
insert @t values(1),(2),(3)

SELECT  has20  = myCrossApply.chk1, 
        has30  = myCrossApply.chk2,
        mynum
FROM   @t tmp
CROSS APPLY (
SELECT 
  max(case when h = 20 then 1 else 0 end) chk1, 
  max(case when h = 30 then 1 else 0 end) chk2
FROM
(   SELECT h = tmp.myNum * 10
    UNION all
    SELECT h = tmp.myNum * 20 
    UNION all
    SELECT h = tmp.myNum * 30) x
) myCrossApply

Result:

has20   has30   mynum
1       1       1
1       0       2
0       1       3