November 25, 2003 at 12:09 pm
I'm trying to write a static sql using CASE.
The db name however will be dynamic.
for eg
declare @db_nam varchar(40)
set @db_nam = 'newdb.dbo.'
select name
from
case @db_nam
WHEN 'newdb.dbo.' THEN newdb.dbo.namelist
ELSE olddb.dbo.name
End
order by name
When I try to execute the above query, I encounter errors .
It says...
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'case'.
How can I get this to work ?
Thanks.
November 25, 2003 at 12:27 pm
You can not use CASE in this context. Case can only be use in the select statement but not the from section...
However you could try this way:
declare @db_nam varchar(40)
set @db_nam = 'newdb.dbo.'
if (@db_nam = 'newdb.dbo.')
begin select name from newdb.dbo.namelist
order by name
end
ELSE
begin
select name from olddb.dbo.name
order by name
mom
November 25, 2003 at 1:26 pm
case CAN be used in:
- SELECT List
- Join Conditions
- ORDER BY
- GROUP BY
- WHERE
- Some String Handling expressions
Clauses but
not on the FROM part
* Noel
November 25, 2003 at 1:57 pm
Or also can try this:
declare @db_nam varchar(40)
set @db_nam = 'newdb.dbo.'
EXEC ('select name from ' + @db_nam + 'namelist order by name ')
In wich won't matter the db name.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply