[SOLVED] Use dynamic database name in a stored procedure

Use dynamic database name in a stored procedure

I'm trying to create a SQL query to dynamically change database name passed in as @name variable, when trying to execute the stored procedure, I get an error message.

CREATE OR ALTER PROCEDURE [schema].[procname]
    (@name varchar(20))
AS
BEGIN
    DECLARE @sql nvarchar(max);

    SET @sql = N'SELECT ID, Name, [col1], [col2]
                 FROM @name.[schema].[table]';

    EXEC sp_executesql @sql;
END
GO

EXEC [schema].[procname] 'DVMTIONAPP'

I get this error:

Must declare the scalar variable "@name".

Solution

SQL Server won't automatically replace the @name placeholder (inside a string) with the content of the variable.

You have to explicitly concatenate the strings. For avoiding code injection, you should use QUOTENAME().

Something like:

SET @sql = N'SELECT ID, Name, [col1], [col2]
                 FROM ' + QUOTENAME (@name) + N'.[schema].[table]';

For more complex cases, in which you're passing a multipart identifier ("DB.TABLE", etc), you could use DB_ID(), DB_NAME(), OBJECT_ID(), OBJECT_NAME(), etc.

For non-entities (like values in a WHERE clause, etc), in dynamic SQL, again avoiding injection, parameters are the way to go.