Posted by Ed Murphy on 06/15/07 01:32

I'm working on a script to convert data from one software package
to another. Greatly simplified, it looks something like

create procedure import_widget as
begin

insert into our_widget (foo, bar)
select baz, quux from their_db.dbo.their_widget

end
go

The problem is that the name of the source database varies from
one system to another, so I want to pass the database name as a
parameter. I think I could do the following, but is there a
better way to go about it?

create procedure import_widget (@db_name sysname) as
begin

exec 'create view their_widget as select * from '
+ @db_name + '.dbo.their_widget'

insert into our_widget (foo, bar)
select baz, quux from their_widget

drop view their_widget

end
go

[Back to original message]