A database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.
The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.
An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.
As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.
If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.
Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"