The following is a clever HACK that does exactly what the OP is asking. We use something like this in-house that allows QA testers to pull data from any number of a list of client databases into a mock testing database where the stored procedure actually resides.
To demonstrate this example I executed the following on several databases accessible from my server just to create a table to test against
select * into MyTestTable from sys.all_columns
I then created the following stored procedure on my target database. I commented it I think well enough for someone to pretty much get what's going on. I won't argue the merits and/or dangers of this approach. It works. It doesn't require dumping all of your code into dynamic SQL strings, etc.
create procedure AnyDbStoredProcedure
@dbname varchar(200),
@count int
as
-- You can add additional parameters as needed above.
-- Modify the call to sp_executesql at the bottom of the
-- stored procedure as needed to include or exclude parameters.
set nocount on
-- MARK THE START OF THE STORED PROCEDURE CODE YOU
-- ACTUALLY WANT TO EXECUTE WITH A COMMENTED GUID.
-- (SEE BELOW)
declare @guid varchar(38) = '-- {5E105697-D144-4073-A1A6-330A264159DF}'
-- You can call your stored procedure what you like,
-- but its name must be copied to the parameter here.
declare @sp_name nvarchar(200) = 'AnyDbStoredProcedure'
-- EVERY DATABASE OBJECT YOUR SCRIPT REFERENCES MUST
-- INCLUDE THE FULLY QUALIFIED DATABASE NAME.
-- WHATEVER DATABASE YOU USED, PUT ITS NAME HERE:
declare @dbname_to_replace varchar(20) = '[FloridaDev]'
declare @spbody table (
line_num int not null identity(1,1),
[Text] varchar(max))
declare @sql nvarchar(max) = '', @params_sql nvarchar(max) = ''
declare @text varchar(max), @sp_body_started int = 0, @in_params bit = 0
insert @spbody ([Text])
exec sp_helptext @sp_name
declare csr cursor fast_forward for
select [Text] from @spbody
order by line_num
open csr
fetch next from csr into @text
while @@FETCH_STATUS = 0
begin
if LEFT(@text, 16) = 'create procedure'
begin
set @in_params = 1
end
else if @text = 'as' + char(13) + char(10) and @in_params = 1
begin
set @in_params = 0
end
else if @in_params = 1
begin
set @params_sql = @params_sql + @text
end
else if CHARINDEX(@guid, @text) > 0
begin
-- Ignoring the first instance found which is in
-- the variable declaration above.
set @sp_body_started = @sp_body_started + 1
end
else if @sp_body_started = 2
begin
set @text = REPLACE(@text, @dbname_to_replace, @dbname)
set @sql = @sql + @text
end
fetch next from csr into @text
end
close csr
deallocate csr
set nocount off
print '== PARAMS =='
print @params_sql
print '============'
print @sql
-- BE SURE TO PASS YOUR ADDITIONAL PARAMETERS IF YOU HAVE ANY.
exec sp_executesql @sql, @params_sql, 'dummy_for_@dbname', @count
return
-- AS MENTIONED AT THE TOP, MARK THE START OF THE STORED PROCEDURE
-- CODE YOU ACTUALLY WANT TO EXECUTE WITH A COMMENTED GUID.
-- {5E105697-D144-4073-A1A6-330A264159DF}
select object_name(object_id), name
from (
select *, row = row_number() over (order by object_id, column_id)
from [FloridaDev].dbo.MyTestTable
) as tmp
where tmp.row <= @count -- EXAMPLE USES EXTRA PARAMETER
go
exec AnyDbStoredProcedure 'Florida', 20
