title: CROSS APPLY does not work with SQL SERVER 2000?
body: <p>How can I use something equivalent of CROSS APPLY in SQL Server 2000 ?</p>
  
  <p><strong>I have a function which returns top level parent of id passed.</strong></p>
  
  <pre><code>ALTER Function [dbo].[fn_GetTopParentRiskCategory]
  (
   @RctId int
  )
  RETURNS @TEMP_TABLE TABLE
  (
   rctId int,
   topParentRiskCat Varchar(100)
  )
  AS 
  BEGIN
    DECLARE @PARENTID INT
    DECLARE @GRANDPARENTID INT
    DECLARE @CODE VARCHAR(100)
    DECLARE @DESC VARCHAR(100)
    DECLARE @PARENTCODE VARCHAR(100)
  
  
    SELECT @PARENTID= rctParentID from RiskCategory where rctid=@RctId
  
    SELECT @GRANDPARENTID= rctParentId from RiskCategory where rctid= @PARENTID
    IF @GRANDPARENTID IS NULL
    BEGIN
     SELECT @CODE='ALL'
     INSERT INTO @TEMP_TABLE SELECT @Rctid,@CODE
    END
    ELSE
    BEGIN
     SELECT @CODE=''
     SELECT @DESC=rctDescription from RiskCategory where rctid=@PARENTID
     WHILE(@PARENTID IS NOT NULL)
     BEGIN
       SELECT @PARENTCODE=rctCode from RiskCategory WHERE rctid=@PARENTID
       IF @PARENTCODE='All'
        SET @PARENTCODE=''
       SELECT @CODE=@PARENTCODE +'.'+ @CODE 
       SELECT @PARENTID =rctParentID from Riskcategory where rctid= @PARENTID 
     END
      SELECT @CODE=Substring(@CODE+@DESC,2,len(@CODE+@DESC))
      INSERT INTO @TEMP_TABLE SELECT @RctID,@CODE
    END
  
  RETURN 
  END
  </code></pre>
  
  <p>Used in query as follows</p>
  
  <pre><code>      INSERT INTO @TopRiskCat
     SELECT R.rskid
         ,a.topParentRiskCat AS TopLevelRiskCat
     FROM RISKS R 
     INNER JOIN RiskAnalysis RA
     ON R.rskId = RA.ranRiskId
     INNER JOIN RiskCategory RC
     ON RA.ranRiskAnalId = RC.rctId
     CROSS APPLY fn_GetTopParentRiskCategory(RC.rctid) as a
  </code></pre>
  
  <p>It does not work in 2000. I have release tomorrow . Please help !!</p>
  
  <p>I'll be grateful</p>
  
  <p>Thanks</p>