T-SQL Declaring Connection String as a Parameter

Question

DECLARE @DBS nvarchar(32)
SET @DBS = 'Current' --'Archive'
SELECT TOP 100 *
  FROM [@DBS].[dbo].[table]

I have 2 structurally identical databases that sit on the same server. One only saves information for 30 days, for quick report processing, the other saves years worth of information. Either query executes fine if I have Current.dbo.table or Archive.dbo.table, but I'm wanting to set a parameter so we can toggle between Current and Archive, within the same report, so a user can pull from the Archive database without having to have admin access to switch the data source connection string. Is this possible to do? I'm certain the syntax error is because the connection string is not a nvarchar, but I can't figure out the proper way to write this query.

Potential duplicate of https://stackoverflow.com/questions/40507374/declare-the-server-name-as-a-variable-in-ms-sql-server-2016 — deadlydog, Oct 17 '22 at 17:03

score 1 · Accepted Answer · answered Nov 07 '16 at 22:16

1

For SQL Server you'll need Dynamic SQL.

DECLARE @DBS nvarchar(32)
SET @DBS = 'Current' --'Archive'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
SELECT TOP 100 *
  FROM ' + @DBS + '.[dbo].[table]'
EXEC(@SQL)

answered Nov 07 '16 at 22:16

S3S

24,809
5
26
45

score 0 · Answer 2 · answered Nov 07 '16 at 23:08

you can try this. Based on your parameter value the individual selects return either all rows or nothing.

SELECT TOP 100 *
  FROM [DBS].[dbo].[table] -- Your active table schema
 where @yourParamenter= 0
union
SELECT TOP 100 *
  FROM [archiveDBS].[dbo].[table]   -- Your archive table schema
 where @yourParamenter= 1

T-SQL Declaring Connection String as a Parameter

2 Answers2

Linked