CODEWITHSUNDEEP
sqlsql-servert-sql
s-a-n
s-a-n

Reputation: 787

Using Hyphen in Variable

DECLARE @query varchar(MAX)
DECLARE @dbinstance varchar(200)
DECLARE @dbname varchar(200)

SET @dbinstance = 'this-is-servername\databaseinstancename'
SET @dbname = 'databasename'

SET @query = 'select column1, column2 from +@dbinstance+'.'+@dbname+'


EXEC(@query)

I am getting the following Error

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

If a servername has hyphen cant we use it in the variable? If so is there a work around for this as I'm not allowed to change the server name but have to use it in a variable.

Upvotes: 2

Views: 3655

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221370

You have to quote your identifier as such:

[this-is-servername\databaseinstancename]

E.g.

SET @query = 'select column1, column2 from ['+@dbinstance+'].['+@dbname+']'

If your identifiers are likely to contain ] you will also need to escape that as such:

REPLACE(@identifier, ']', ']]')

An alternative would be to use the built-in QUOTENAME function as indicated by Damien_The_Unbeliever in the comments.

If your identifiers cannot be trusted (e.g. user input), you MUST escape them to prevent SQL injection.

Upvotes: 5

Related Questions