How to Set value dynamically in setvar variable SQL

Question

I am using SQLCMD mode. I want to set setvar variable value. How should I set value in :setvar? My Code are look like this :

DECLARE @DatabaseName AS nvarchar(180)
set @DatabaseName = '$(dbName)'    
print @DatabaseName     
:setvar DatabaseName @DatabaseName 

Answer 1

Please see below documentation https://learn.microsoft.com/en-us/sql/relational-databases/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-2017

and below code from above

sqlcmd
:setvar  MYDATABASE AdventureWorks2012
USE $(MYDATABASE);
GO

so based on above your code should work fine...

Answer 2

To get current using database use code bellow:

DECLARE @DatabaseName AS nvarchar(180)
SELECT @DatabaseName = DB_NAME()


print @DatabaseName     
:setvar DatabaseName @DatabaseName