if...then? case?

[JahDW]

I have a query that accepts two parameters, location and service. I need the query to select from a different table depending on the service the user selects (a drop down box in SQL Reporting Services). I've tried to use a case statement in the from clause to no avail, and I can't seem to get an if...then to work either.

here's a general example:

select column1, column2, etc
from VARIABLE
where service = @PARAMETER and location = @PARAMETER

The service and location are supplied by the user. the VARIABLE must change based on the service.

For instance, if the service is SAV, then the table (VARIABLE) must be 'no_sav'

any input will be greatly appreciated!

 

[gmmastros]

If @Service = 'SAV'
Begin
select column1, column2, etc
from no_sav
where service = @PARAMETER and location = @PARAMETER
End

If @Service = 'Something Else'
Begin
select column1, column2, etc
from AnotherTable
where service = @PARAMETER and location = @PARAMETER
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NoCoolHandle]

Or maybe
If @Service = 'SAV'
Begin
select column1, column2, etc
from no_sav
where service = @PARAMETER and location = @PARAMETER
End
[red]Else[/red] If @Service = 'Something Else'
Begin
select column1, column2, etc
from AnotherTable
where service = @PARAMETER and location = @PARAMETER
End

 

[JahDW]

yes! it works now, EXCEPT...

When I run it the first time it's fine. then when I modify the parameters (choose different service or location from the dropdown box) and run it again I get an error.

...Probably a Reporting Services issue.