Select CTE query statement based on parameter value

[robertx]

Using SQL Server, is there a way to select a CTE query statement based on the value of a parameter passed?

For example:

@MyValue bit

WITH cte AS
(
IF @MyValue='True' THEN
(SELECT.....)
ELSE
(SELECT.....)
END
)

I have tried this and also "SELECT CASE WHEN" but I can't get it to compile. It fails with errors:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
No column name was specified for column 1 of 'cte'.

[GaryMazzone]

I would write 2 ctes and then select from one of them. The cte can't be in the if statement. The error with no name for column1 means as an AS to the column

[techgnome]

What's the difference between the two select statements? Are they fundamentally the same, jsut different fields? OR are they completely different tables to begin with? Why would one get used vs the other? Depending on what the intentions are, there may be alternatives to the CTE.

-tg

[robertx]

I would write 2 ctes and then select from one of them. The cte can't be in the if statement. The error with no name for column1 means as an AS to the column

I contemplated this but wondered whether it might create overhead pulling a whole dataset that is not ultimately required when dealing with a large number of records.

[robertx]

What's the difference between the two select statements? Are they fundamentally the same, jsut different fields? OR are they completely different tables to begin with? Why would one get used vs the other? Depending on what the intentions are, there may be alternatives to the CTE.

-tg

In this instance they are the same query except the second one has a WHERE clause to only pull certain records that match a criterion. I could probably just pull all of the records and do the filtering in the main query or as GaryMazzone suggests using 2 CTE tables and selecting the one I want in the main query. However, I am just concerned that these options might be creating overhead with large datasets that could be overcome if it were possible to use an IF or SELECT CASE statement within a CTE.

The WHERE clause in the second query simply filters by a bit field "WHERE MyValue=@MyValue". I can achieve what I want using one CTE table with "WHERE MyValue='True' OR MyValue=@MyValue" so if MyValue is 'False' then all records are returned but I am still interested to understand more about the principles of performance and whether selecting from multiple queries in a CTE is possible.

[techgnome]

Yes it's possible... but that means running BOTH queries... so you're actually losing performance... Or something like that. The CTE gets created as an in-memory table, so it's going to have some cost if you have two of them.

Personally I's use a where clause that filters based on the parameter, or where the parameter is null:
WHERE (Field1 = @Param2) or (@Param1 is null) ... if I then pass in a null, then it returns all rows, or if I pass in a value, then it filters on that value.

Another alternative is to move the if outside the CTE:

If @PAram = 'some value'
  With yourCTE as (Select blah blah)... etc. Where yada yada yada
  select and so on
Else
  With yourCTE as (Select blah blah)... etc. 
  select and so on

That works too... personally I try not to have branches like that... in 6 months when it comes to maintain that, you have to make changes in two places.

-tg