How to solve this SQL Server error I'm getting?

Question

As you can see below I am trying to execute a stored procedure by passing the two parameters, I get error like

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'C:\SQL_Backup\Test.bak'.

Code:

EXEC Proc_RestoreSQL_Database ('C:\SQL_Backup\Test.bak','DMTest')

Stored procedure being executed:

ALTER PROCEDURE [dbo].[Proc_RestoreSQL_Database]
    @DISK nvarchar(1000),
    @DBName varchar(1000)
AS
BEGIN
    -- Create DB with script(DMUtilityTest)
    CREATE DATABASE [@DBName]

    -- restore
    RESTORE DATABASE [@DBName] 
    FROM DISK = N'@DISK' 
END

Answer 1

There is no need to CREATE the database if you are immediately going to restore it.

Just execute the restore directly. Do not wrap the database name parameter in [], nor the filename parameter in quotes '', you only need this if you are embedding the actual name, not parameters.

You should also use more sensible parameter types

CREATE OR ALTER PROCEDURE [dbo].[Proc_RestoreSQL_Database]
    @DISK nvarchar(255),
    @DBName sysname
AS
    RESTORE DATABASE @DBName
    FROM DISK = @DISK;

GO

This obviously assumes you are going to restore to the same location. You may want to pass new locations using MOVE TO @newFile

---

To execute it, again do not wrap in parenthesis (), just specify the parameter names

EXEC Proc_RestoreSQL_Database @DISK = 'C:\SQL_Backup\Test.bak', @DBName = 'DMTest';