I am trying to change database dynamically using variable, but not able to do it. Following is the script
set @query='Use {DBNAME}'
DECLARE @SQL_SCRIPT VARCHAR(MAX)
SET @SQL_SCRIPT = REPLACE(@query, '{DBNAME}', @DatabaseName)
EXECUTE (@SQL_SCRIPT)
before this script i have variable @DatabaseName set with diff database name
There is nothing wrong with your script. The EXECUTE is setting new context to the database but you need to append the the other code to the dynamic T-SQL statement as well.
DECLARE @SQL_SCRIPT VARCHAR(MAX)
DECLARE @DatabaseName SYSNAME
DECLARE @query NVARCHAR(MAX)
SET @DatabaseName = 'master'
set @query='Use {DBNAME}; select db_Name();'
SET @SQL_SCRIPT = REPLACE(@query, '{DBNAME}', @DatabaseName)
EXECUTE (@SQL_SCRIPT);
This will give you master. So, you can change the context, just add the rest of the code there, in the same T-SQL statement.
Also, you are not allowed to use GO in EXECUTE/sp_executesql.