Here is my query:
IF EXISTS(%some query%)
ALTER DATABASE [MyDatabase] SET single_user WITH ROLLBACK IMMEDIATE;
--throw some exception
--here I want to set database back to multi_user
RAISERROR ('DBErrorMessage', @ErrorSeverity, @ErrorState);
Database 'MyDatabase' is already open and can only have one user at a time.
ALTER DATABASE [MyDatabase] SET MULTI_USER
It's been a while, but I believe the
with rollback immediate option is there to say you want this to succeed no matter what. the normal behavior blocks until all running transactions have completed successfully.
Also, setting the database to multi-user mode when it's already multi-user is safe.
However, if you want to be really sure, use a nested try catch block. The first one to handle any error moving to single user mode, and the inner one to handle errors that occur in single-user mode..
A couple of final notes for completeness:
1) If the error is severe enough your connection will be closed, and your database will still be in single-user mode.
2) You should have a test instance where you can safely try this stuff out, so that you can learn what's going to happen in production.