SQL Server: how to get a database name as a parameter in a stored procedure

A different way to the same end is to use a system stored procedure.

See SQL Stored Procedure(s) - Execution From Multiple Databases.

If the procedure name starts with "sp_", is in the master db and marked with sys.sp_MS_MarkSystemObject, then it can be invoked like this:

Exec somedb.dbo.Test;
Exec anotherdb.dbo.Test;

Or like this:

Declare @Proc_Name sysname;
Set @Proc_Name = 'somedb.dbo.Test';
Exec @Proc_Name;

Parameters can be used too.

Using this technique requires using the 'sp_' prefix and putting code into a system database. It is your choice if that offsets not using dynamic SQL.

or use Powershell to run a script and passs the dbname

    # Script #1 - clear the existing CLR functions, sps, and assemblies 
    $scriptFile = "Script_Drop_Functions_and_Other_Programmability_Objects.sql";

    # Standard PARAMETERS to the PS cmdlet Invoke-Sqlcmd - these are splatted here to standardize.  This PARAMETERS includes the standard flags e.g. ServerInstance as well as the variables for each script
    $sqlcmdParameters = @{
        ServerInstance = $serverName #cmd flag
        Database       = $dbName #cmd flag
        InputFile      = $scriptFile #cmd flag, changes for each invocation
        Variable       = $sqlCmdVariables #cmd flag, changes for each invocation
        Verbose        = $true #cmd flag, set to true by default for additional output detail
    };

    <# PowerShell numbers the types of 'stream' messages according to their type from 1 to 6 - Use these numbers in order to control the returned output: 
    (1) success message, which is the output of the success command execution. This corresponds to SQL Server query results
    (2) Error messages. 
    (3) Warning messages. 
    (4) Verbose messages. This corresponds to SQL Server PRINT statements
    (5) Debug massages. 
    (6) Information massages.  

    Type 1 will be put in file $logFileResults 
    Type 4  will be put in file $logFileOutput  
    
    #>

    (Invoke-Sqlcmd @sqlcmdParameters >> $logFileOutput)  4>> $logFileResults 

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