Search code examples
sqlsql-servert-sqlwhere-clausesql-view

Microsoft SQL server View of multiple conditional statements with UNION

Is there any way to achieve the following behavior in Microsoft SQL server ?

A view with several SQL statements which can be invoked according to parameters passed to the view.

The view should look like this:

if param.contains "a"

  select name from table1

if param.contains "b"

  select name from table2

if param.contains "c"

  select name from table3

Between all the IFs I want to have a UNION, such that if more than one condition is true, the result is a UNION of all these conditions results.

The view will be invoked this way for example (psuedo):

SELECT * FROM myView where param IN {"a", "b"}
Solution

  • You seem to want:

    create view myview as
select 'a' cat, name from table1
union all select 'b', name from table2
union all select 'c', name from table3

    Then you can query the view like so:

    select * from myview where cat in ('a', 'b');