Distincts databases on the same stored procedure

Hi!

I need to use two distincts databases on the same stored procedure. One, database1, is where I want to place the procedure, and where the table the procedure populates is (table1). The other, database2, hosts the table (table2) from where I select some data to put into table1 in database1.
Database1 name is fixed, while database2 name may change. So, I would like to pass database2 name as a parameter to the procedure. In this simple example, it works fine:

use database1
go
create procedure teste @.db_name varchar(10) as
exec ('select * from ' + @.db_name + '.dbo.table2')

But the problem is that in my procedure, database2 is used in a cursor, something like this:

create procedure myProcedure @.year int, @.pDatabase2 varchar(30) AS

declare ...

WHILE ...
BEGIN
...
declare cursor1 cursor for
select column1, column2 from @.pDatabase2.dbo.table2
where ...

open cursor1
fetch next from cursor1 into ...

close cursor1
deallocate cursor1

-- insert data into table
INSERT INTO table1
VALUES...

....

And I get an error if I try to use the exec! How can I do it? Can anyone help me please?

Thank you!

Try to rebuild your query; either refrain from using cursor, or from using variable for a database.

Also, you can EXEC('select column1, column2 from ' + @.pDatabase2 + '.dbo.table2 INTO ##TempTable'), then run a cursor over TempTable.

|||

You can execute the declare cursor dynamically if you use a global cursor like:

set @.dbname = quotename(@.pDatabase2)

exec ('declare cursor1 cursor global for select column1, column2 from ' + @.dbname + '.dbo.table2')

open cursor1

...

But it seems like what you are doing is unnecessary and complicated. You should instead do the following which is easier to manage.

1. Create a stored procedure in database2 that returns the expected results from table2 like:

create procedure gettable2

as

select col1, col2 from table2

2. And now do insert..exec from database1 like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'gettable2'

Alternatively, you can just use approach in step #2 and issue the SELECT directly like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'select col1, col2 from table2'

The SP is approach is slightly better from a security standpoint since you don't have to give SELECT permission on the table(s). With either approach, you will have to watch out for ownership chaining issues for example. See Books Online for more details on how ownership chaining works and db ownership chaining option.

|||Thank you a lot, it's much easier now!