How to modify / rename the database name in SQL?

Question

How to modify / rename the database name in SQL?

Running the below script throws exception as " Incorrect syntax near @dbname "

Declare @dbname nvarchar(50)
set @dbname = 'MyDatabase_Test'

ALTER DATABASE MyDatabase MODIFY NAME = @dbname;

Answer 1

DECLARE @SQLString nvarchar(500);  
DECLARE @OldDbName nvarchar(100);
DECLARE @NewDbName nvarchar(100);

SET @OldDbName =  'MyTestDatabase';
SET @NewDbName =  'MyNewTestDatabase';
BEGIN TRY

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @OldDbName + ' SET SINGLE_USER WITH ROLLBACK 
IMMEDIATE'; 
        EXECUTE sp_executesql @SQLString

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @OldDbName + ' MODIFY NAME = ' + @NewDbName;
        EXECUTE sp_executesql @SQLString            

        SET @SQLString =  
                    N'ALTER DATABASE ' +  @NewDbName + ' SET MULTI_USER' ;
        EXECUTE sp_executesql @SQLString

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();

END CATCH