-
May 29th, 2017, 11:13 PM
#1
Thread Starter
Frenzied Member
Select CTE query statement based on parameter value
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'.
-
May 30th, 2017, 04:16 AM
#2
Re: Select CTE query statement based on parameter value
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
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
May 30th, 2017, 06:39 AM
#3
Re: Select CTE query statement based on parameter value
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
-
May 30th, 2017, 05:50 PM
#4
Thread Starter
Frenzied Member
Re: Select CTE query statement based on parameter value
Originally Posted by 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
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.
-
May 30th, 2017, 06:03 PM
#5
Thread Starter
Frenzied Member
Re: Select CTE query statement based on parameter value
Originally Posted by 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
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.
-
May 30th, 2017, 10:35 PM
#6
Re: Select CTE query statement based on parameter value
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:
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|