Using CASE to determine the FROM clause

[mdiaz1328]

I am trying to build a procedure that is generic enough that I may use it to query several tables with a single procedure. Most of the tables have pretty much the same columns.

I tried the following but I keep getting an error...
.
.
.
from
CASE @view2use
WHEN 'view1' THEN 'view1_vw'
WHEN 'view2' THEN 'view2_vw'
WHEN 'view2' THEN 'view3_vw'
WHEN 'view4' THEN 'view4_vw'
END
where
.
.
.

Is there a way for me to accomplish this ?

 

[gmmastros]

You cannot use a case statement to determine which table to use. There are a couple alternatives though.

1. You could use dynamic SQL to perform the query.

http://www.sommarskog.se/dynamic_sql.html

2. You could use an IF statement.
EX:

If @view2use = 'view1'
  Begin
    Select Column(s)
    From   view1_vw
  End
Else If @view2use = 'view2'
  Begin
    Select Column(s)
    From   view2_vw
  End
Else If @view2use = 'view3'
  Begin
    Select Column(s)
    From   view3_vw
  End
Else If @view2use = 'view4'
  Begin
    Select Column(s)
    From   view4_vw
  End

3. You could use a Union All query.

Select Column(s)
From   view1_vw
Where  @view2use = 'view1'

Union All

Select Column(s)
From   view2_vw
Where  @view2use = 'view2'

Union All

Select Column(s)
From   view3_vw
Where  @view2use = 'view3'

Union All

Select Column(s)
From   view4_vw
Where  @view2use = 'view4'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom