Samy Sammour Samy Sammour - 25 days ago 14
SQL Question

Declare the server name as a variable in MS Sql Server 2016

I have a big script file and I need to use it on another server. I need to edit the name of the server one time at the beggining using Declare so I can be able to use the same script on multiple servers only by changing the value of the variable.

something like that:

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

SELECT * From @Quell.[Documents]


but it did not work.

how to do it? thank you

Answer

Unfortunately macro substitution is not permitted in SQL Server, but you can use dynamic SQL.

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

Declare @SQL varchar(max) 
SET @SQL = 'SELECT * From ' + @Quell +'.[Documents]'
Exec(@SQL)
Comments