Db Name as parameter in stored procedure SQL Server

Question

I'm looking to pass my database name as a parameter to the stored procedure, and I'm looking to use it in the where condition to set the database of the stored procedure. But I get an error:

Incorrect syntax near '.'

Sample Code

Create proc [dbo].[stored_procedure_one]
    @variable1 int,
    @dbname varchar(10)
as
begin
    select * 
    from @dbname..table_name
End

Can someone suggest me how to solve this?

Answer 1

You will need to use dynamic sql for this something like this.....

Create proc [dbo].[stored_procedure_one]
    @variable1 int,
    @dbname    SYSNAME  --<-- use appropriate data type for object names
as
begin
  DECLARE @Sql NVARCHAR(MAX);

 SET @Sql = N' select * from ' + QUOTENAME(@dbname) + N'..table_name'

 Exec sp_executesql @Sql

End

Also use QUOTENAME() function to protect yourself against possible sql-injection attack.

Answer 2

Just to offer an alternative, it's fun to note that EXEC can take a string as the thing to execute, so for example:

DECLARE @sp nvarchar(255) = N'sys.sp_who2';
EXEC @sp;

It can also take parameters, e.g.

DECLARE @sp nvarchar(255) = N'sys.sp_who2';
EXEC @sp 'active';

So we can dynamically build the context where we run a command by using:

DECLARE @dbname sysname = N'tempdb';
DECLARE @context nvarchar(1000) = QUOTENAME(@dbname)
  + N'.sys.sp_executesql';

DECLARE @sql nvarchar(max) = N'SELECT DB_NAME();';

EXEC @context @sql;

And you can pass parameters, too:

DECLARE @dbname sysname = N'tempdb';
DECLARE @context nvarchar(1000) = QUOTENAME(@dbname)
  + N'.sys.sp_executesql';

DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(), @x;';

EXEC @context @sql, N'@x int', 5;

This approach really simplifies things like concatenating the database name all over the place, avoiding db-specific functions like object_name, and ensures that your entire command runs in that other database. You can also do it across linked servers, e.g.:

DECLARE @server sysname = N'linked_server';
DECLARE @dbname sysname = N'tempdb';
DECLARE @context nvarchar(1000) = QUOTENAME(@server)
  + N'.' + QUOTENAME(@dbname)
  + N'.sys.sp_executesql';
...