I have a collection of stored procedures that I use for data migration purposes. I have to run this collection of procs on a number of different databases, so what I would like is to be able to pass the database name into each proc, instead of retyping each of them. or having different copies of the procedures about the place.
i.e each statement contains the reference to the DB (INSERT INTO DATABASENAME.dbo.TABLENAME)
Ideally, some sort of global variable would be good - that I could refer to in each stored proc, and then just pass it down to them all in an overarching proc.
Can this be done? and how does the syntax look when referring to it - is it @databasevar.dbo.TABLENAME - or do I need to create it all as one string, and then execuite the string.