Temp Table in Stored Proc that can pull data from two databases 1

[CarolCook]

Hi:
I need to create a stored procedure in one database that depending on the passed parameter, pulls either a value from that database or a second database.

To boil it down, the code of the table population looks like this so far:

INSERT #TEMP
(mydescription)
select mydescription from table
select * from #temp

but what I'm looking for is more like this for the table population select :

select mydescription from
case when parameter=1 then dbONE.dbo.table.value
else dbTWO.dbo.table.value

which doesn't work. I have also tried to embed a begin and end inside the select and SQL doesn't like that either.

Any advice is greatly appreciated

 

[CarolCook]

Have also tried:

select case when parameter=1 then dbONE.table.value else
dbTWO.table.value

the error message tells me that the second table cannot be bound.

 

[Crowley16]

you can't do it like that, you'd need to join the 2 tables first, something like:

select
case param
when 1 then a.value
else b.value
end
from
db1..table1 a
inner join db2..table2 b on a.id = b.id

--------------------
Procrastinate Now!

 

[gmmastros]

something like this maybe?

Create Table #Temp (myDescription VarChar(20))

If @Paremeter = 1
  Begin
    INSERT #TEMP
       (mydescription)
    select mydescription from [!]dbOne.dbo.[/!]table
  End
Else
  Begin
    INSERT #TEMP
       (mydescription)
    select mydescription from [!]dbTwo.dbo.[/!]table
  End

select * from #temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

yeah, if the data doesn't have appropriate keys to join on, then you can't do what I previously said, use George's solution instead...

--------------------
Procrastinate Now!

 

[CarolCook]

George:
THANKS SO MUCH. This works perfectly and enables me to create database independent XML!
Carol