SQL Server Performance

Conditional Union

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by rklimes, Apr 28, 2008.

  1. rklimes New Member

    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

  2. MohammedU New Member

    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
  3. rklimes New Member

    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

  4. Adriaan New Member

    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.
  5. moh_hassan20 New Member

    use stored procedure to handle if ..else logic
  6. MichaelB Member

    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

Share This Page