I have an existing SP. I want to start using database snapshots in some cases. Users can save a snapshot at any time. If they do, the SP should use it. Otherwise, use the primary database.
I want to adapt the SPs to handle this, by making the database/table names dynamic instead of hard-coded in the SP.
I can imagine something like this working, with fully qualified table names, but it gives the error 'Must declare the table variable "@db1"':
declare @table1 varchar(25);
set @table1 = Snapshot.schema.tablename;
select * from @table1;
This gives "Incorrect syntax near '@db'."
declare @db varchar(25);
set @db = "Snapshot";
use @db;
This sorta works, but the "use" is only in effect during the execute. By the time the 'select' is executed, you are back to using the original database.
declare @db varchar(25);
set @db = 'use Snapshot';
EXECUTE(@db);
select * from Schema.Tablename;
I know I could generate the entire SP on the fly, but that seems guaranteed to get rejected by the DBAs. I'd rather a less radical solution.
Any better ideas?
