Sorry for the late reply I've been working around the house and missed your response.
Please allow me a little setup:
Most SQL programmers will tell you that there are two types of CTEs: recursive and non-recursive. While I don't disagree with this assessment I would add a third type (or at the very least a variation on the two formally accepted types): the parametrized CTE. I won't burden you with the specifics as I'm sure you've used the two common CTE forms, and maybe even this third form/variation, which follows:
Set NoCount On;
Declare @Span Int;
;With ZeroCrossingRange1(n) as
Select n+1 from ZeroCrossingRange1 where n<@Span
Select * from ZeroCrossingRange1;
;With RowSource(n) as
-(Select Count(*) from sys.columns)/2+
Row_Number() over (order by (Select Null))
Select * from RowSource where n between -@Span and @Span
Select * from ZeroCrossingRange2
There is no way for the first CTE to generate its row set without the @ variable mediating it's initialization and recursion. The second CTE uses a standard where clause predicate in the CTE instead of on the select statement referencing the CTE. I'm not push this methodology, just show its implementation for my next idea.
So, basically you can get information into your CTEs in one of three ways (or a combination of the three): from table sources, from @ variables, or from constants (Select 1 Union all Select 2 Union All ...).
So, with the above in mind, I would like to do something like this:
Set NoCount On;
;With CTE(Validated) as
Select top 10
Select top (RandomNoOfColumns)
Case Row_Number() over (order by (Select Null)) -- Ordinal column number
when 1 then ''
for xml path('')
(Row_Number() over (order by (Select Null))+Abs(CheckSum(NewId())))%25[RandomNoOfColumns]
from sys.columns -- Just used as a generic row source for column RandomNoOfColumns
Select * from CTE
The code up to the CROSS APPLY simply generates some representative test data. Look at the data values as an unknown number of '*' delimited (pipes are too hard to see) option parameters. The idea being to check the nth option parameter, where n is another column in the parent table, for a specific value or set of values.
The above coded CTE is about a simple as I can make it and still expose the problem I'm trying to solve (reverse the comments in the CTE and you'll get a mocked output of what I'm looking for). I just want it to return a value, say 'Yes' or 'No', based on some recursive code (for the non-deterministic part), using parent column values as controls (I know, this just screams TVF, but that's what I'm trying to get away from).
I use a CROSS APPLY as it permits the use of parent columns in its declaration. The problem I am trying to overcome is being able to use a CTE inside that CROSS APPLY and have it use the parent columns in its definition. This is where it fails. The T-SQL compiler won't late-bind the parent column; it just throws an error as that parent column is not yet defined in a way the compiler can see it...
I hope this is a little clearer.
PeteKI have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.