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”.
4
You can’t substitute names for entities (database, table, column) like that. You can do it insecurely with string concatenation, but this is messy and risky. What you can do is execute in the context of the other database, e.g.:
CREATE OR ALTER PROCEDURE [schema].[procedure_name]
@database_name sysname -- be specific, also not varchar & not 20
AS
BEGIN
DECLARE @exec nvarchar(256),
@sql nvarchar(max);
-- to ensure it's a database that exists:
SET @exec = QUOTENAME(DB_NAME(DB_ID(PARSENAME(@database_name,1))))
+ N'.sys.sp_executesql;';
IF @exec IS NOT NULL
BEGIN
SET @sql = N'SELECT ID, Name, [col1], [col2]
FROM [schema].[table];';
EXEC @exec @sql;
END
END
GO
EXEC [schema].[procname] @database_name = N'DVMTIONAPP';
What ends up happening behind the scenes is this will execute:
EXEC [DMDVMTIONAPP].sys.sp_executesql
N'SELECT ID, Name, [col1], [col2]
FROM [schema].[table];
This avoids SQL injection as it simply won’t have anything to execute if @database_name isn’t valid (unless someone created a database called something like ].db.schema.table; DELETE db.schema.table;, but then the query would be populated but executing it would fail).
It is also better equipped to handle cases where you specify the database name multiple times (e.g. a join in an arbitrary database), because the query is static and you only define/express the database name once.
It also makes the query inside the string easier to read and easier to debug independently.
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.
0
Please try the following solution.
SQL
CREATE OR ALTER PROCEDURE [schema].[procname]
( @name sysname )
AS
BEGIN
DECLARE @sql nvarchar(max);
SET @sql = FORMATMESSAGE(N'SELECT ID, Name, [col1], [col2]
FROM %s.[schema].[table];'
, QUOTENAME(@name));
EXEC sp_executesql @sql;
END
GO
2