I have a simple requirement where I need to replace a variable with the database name coming from another table.
I have a table called config setting
Key value
ClubcardDB ClubardDetailsDB
Code
Declare @Value varchar(100)
select @Value= value configsetting where Code=’ ClubcardDB’
Select top 10 * from [@Value].[dbo].[ClubcardDetails]
it is giving an error: Invalid object name '@Value.dbo.ClubcardDetails'.
I do not want to build and dynamic query..
Try to use SQLCMD Mode. In SSMS, click Query -> SQLCMD Mode:
:setvar dbname "YourDB"
SELECT * FROM $(dbName).dbo.YourTable
Here is a working example that I made on my machine:
Declare @Value varchar(100)='Test'
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='Select * from ' + QUOTENAME(@Value) + '.[sys].[all_columns];'
EXECUTE sp_executesql @SQL
A slightly safer method as it verifies the value is actually a database name:
Declare @Value varchar(100)='Test'
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL='Select * from ' + QUOTENAME(name) + '.[sys].[all_columns];'
FROM master.dbo.sysdatabases
WHERE name=@Value
EXECUTE sp_executesql @SQL
QUOTENAME at least. A bracket can easily be escaped by someone malicious. For example SET @Value = N'master].sys.objects; CREATE LOGIN t WITH PASSWORD=''1'',CHECK_POLICY=OFF,CHECK_EXPIRY=OFF;--''...FROM ' + QUOTENAME(@value) + '.sys...'?