Calling other databases from stored procedure

Hi,

I am working with multiple databases on the same server and in a stored procedure I need to be able to call on one of them.

Here is an example of what I am trying to do in this stored procedure:
create procedure sp_procedure
(
@.variable int
)
select anitem from atable where selection = @.variable

declare @.anothervariable Char(3)

select @.anothervariable = item_that_determines_database from atable where selection = @.variable

usedbo.@.anothervariable
select count(id) from Some_table where selection = @.variable

The database is not found using this method and I do need to use it in a stored procedure. All of the databases being used are (3) letter names in lower case (aaa, bbb, ccc, etc...), the info that @.anothervariable pulls from the table is the name of that database but in all caps. Does this part make a difference?

Also, what method could I use to get the database variable to read from that selected database?

Thank-you for your help.

Eric

You can build a dynamic SQL.

Declare @.sql varchar(500)

SET @.sql = 'SELECT @.anothervariable = ' + @.otherdb + '.dbo.itemfromothertable where selection = @.variable'

use sp_ExecuteSQL the execute the SQL and add the parameters appropriately.

|||

I don't get it.

Where did the @.otherdb come from?

I am selecting an item from a table and using that item to get a count from a table in another database. The database is determined from the result of the first query.

|||Yes, since the DB name is dynamic, you need to get the name from your first query, assign it to the variable @.otherdb, build your T-SQl and execute it.