I was wondering if there is a way to have the result of a case statement be a t-sql statement within a union? If not, is there another way to accomplish a conditional union? IE. select a, b from table_a union select a,b from table_b union CASE when id = 1 then select a,b from Table_c else select a.b from table_d
UNION cluase is to used to combine two statements results... You can use CASE statement in WHERE clause...may be you are looking that one... SELECT * FROM T WHERE col1 = CASE WHEN col2 < col3 THEN col2 ELSE col3 END
I understand what the two statements (union and case) do. What i am trying to accomplish is have a conditional union statement that depending on certain criteria it would union one statement instead of another. If I cant do a conditional Union my next thought would be if-else. The entire statement is going to be used as a view. can if-else be used in view? ex create view view_a(id, col_a,col_b,col_c,col_d) AS if (id = 1) BEGIN select * from table_a union select * from table_b union select * from table_c END ELSE BEGIN select * from table_a union select * from table_b union select * from table_d END
Check out the CREATE VIEW syntax in BOL - there are no parameters for views. Why not create a stored procedure? To keep this sort of code clean, without having to go spell out every possible combination of queries, use dynamic SQL - see http://www.sommarskog.se/dynamic_sql.html for some caveats.
I agree with Moh_hassan20. But I would suggest that you use the if logic and just insert into a temp table for each section rather than repeat the logic for the other queries for each IF statement. Like : create table #blah (field1....) insert into #blah select * from table_a union select * from table_b if (id = 1) BEGIN insert into #blah select * from table_c END ELSE BEGIN insert into #blah select * from table_d END select * from #blah