SP_EXECUTESQL with database as variable in from clause

April 3, 2023

Is it possible to pass parameter into sp_executesql to determine the source of my query? The goal here is to recieve db name as parameter and sql text with placeholder as parameter and combine them together. I can use REPLACE probably but then I won’t get the parameter safety check.

Code:

DECLARE @my_db VARCHAR(30) = 'MyDatabase'
DECLARE @sql NVARCHAR(MAX) = N'select top 10 * from @db.dbo.table1'


exec sys.sp_executesql @stmt = @sql, @params = N'@db varchar(120)', @db = @my_db;

Result:

Incorrect syntax near ‘.’.

I’m using SQL Server 2016

>Solution :

You, sort of can parametrise the database name here. Instead, define the proc name as a variable:

DECLARE @DBName sysname = N'MyDatabase'
DECLARE @Proc nvarchar(500) = QUOTENAME(@DBName) + N'.sys.sp_executesql';
DECLARE @SQL nvarchar(MAX) = N'SELECT TOP 10 * FROM dbo.table1 ORDER BY SomeColumn;'; --You need an ORDER BY for reliable results


EXEC @Proc @SQL;