SQL variable for Database Name

Question

I am trying to pass a database name in as a parameter and execute some dynamic SQL. As a test I created this:

declare @HRMSDatabase_1 nvarchar(50) = N'FirstDatabase',
        @Example_1 nvarchar(max) =
'select @HRMSDatabase'

execute sp_executesql @Example_1, N'@HRMSDatabase nvarchar(50)', @HRMSDatabase_1

which returns FirstDatabase as I expected.

When I try this:

declare @HRMSDatabase_2 nvarchar(50) = N'FirstDatabase',
        @Example_2 nvarchar(max) =
'select 
      ''Test''
from 
    @HRMSDatabase.dbo.hrpersnl hp'

execute sp_executesql @Example_2, N'@HRMSDatabase nvarchar(50)', @HRMSDatabase_2

I get an error message:

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '.'.

Is what I am trying to do possible? I cannot simply use a USE FirstDatabase as I have a few databases I have to query in the same dynamic SQL using inner joins.

Also, I cannot use SQLCMD as this script gets executed from a GUI.

Also, I cannot use SQLCMD as this script gets executed from a GUI. - that doesnt explicitly stop you using SQLCMD! Its wrapped up as a callable utility: learn.microsoft.com/en-us/sql/tools/sqlcmd-utility — Jamiec
– Jamiec, Commented Apr 25, 2017 at 14:31
Wouldn't from @HRMSDatabase.dbo.hrpersnl hp' need to be from '+ @HRMSDatabas+'.dbo.hrpersnl hp' for the variable to be evaluated in the dynamic SQL? — xQbert
– xQbert, Commented Apr 25, 2017 at 14:32
Usually a sign of a broken data model - you have multiple structures (here, databases) that you wish to treat identically for querying. This usually indicates that they should in fact be a single data structure and the attributes that have become embedded in the object names should instead be present in the data where it's easily queried. (E.g. rather than Sales2015 and Sales2016 databases, there should be a Sales database and then the year (or date) information should be present in appropriate column(s) in the relevant table(s)) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 25, 2017 at 14:35
You can't parameterize identifiers. Not in Sql server and probably not in any self-respecting rdbms. — Zohar Peled
– Zohar Peled, Commented Apr 25, 2017 at 14:38

Jodrell · Accepted Answer · 2017-04-25 14:44:11Z

1

Basically, I don't believe you can parameterize the database name in the table specifier. Instead try this,

DECLARE @HRMSDatabase NVARCHAR(50) = N'FirstDatabase';
DECLARE @Example3 NVARCHAR(MAX) ='SELECT 
        ''Test''
    FROM 
        ' + QUOTENAME(@HRMSDatabase) + '.[dbo].[hrpersnl] hp';

EXEC sp_executesql @Example3;

As you'll note, it's important that the @HRMSDatabase is not recieved from user input as this would be susceptible to injection attacks.

edited Apr 25, 2017 at 14:44

answered Apr 25, 2017 at 14:36

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien_The_Unbeliever Over a year ago

If you're going to the effort of enclosing the database name in []s, you probably ought instead to be passing it through the QUOTENAME function.

Collectives™ on Stack Overflow

SQL variable for Database Name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related