IF statement not being evaluated

I'm writing a script which will automate a particularly long and annoying process. I want to 'hard exit' from the script by using an if... return to evaluate a gate variable (@iHaveSetTheTwoImportantVariablesCorrectly in the example below) and exit unconditionally if the variable evaluates to 'No' (after printing a helpful comment). I've used this technique in plenty of other scripts with no issues.

The if... return block is not being evaluated at all. No matter what I've tried it won't be evaluated. I know it's something to do with the code that comes after it, as if I remove all the code afterwards it will be evaluated.

The purpose is to ensure that I or another user have set the variables before running the script.

I've spent quite a while testing different things to try and make this work.

The code after the if... return check contains some known '...does not exist...' errors due to objects that don't exist yet but will be created during execution, and thus won't produce an error.

I'm using SSMS version 18.10

Here's a heavily modified example of the script, which retains the structure of the original and all relevant code, to give you an idea of what it looks like, the actual script is much longer:

```
--(multi-line comment block with explanation of script)

set nocount on

declare @variableOne varchar(20) = 'some_text_one'
declare @variableTwo varchar(20) = 'some_text_two'
declare @iHaveSetTheTwoImportantVariablesCorrectly varchar(3) = 'No' --set this to Yes when vars are set correctly

declare @otherVariableOne varchar(10) = 'some text'
declare @otherVariableTwo varchar(10) = 'some other text'
declare @otherVariableThree varchar(10) = 'some more text'
declare @aBitVariable bit = 0
declare @anotherBitVariable bit = 1

if @iHaveSetTheTwoImportantVariablesCorrectly <> 'Yes'
begin
print 'The variable @iHaveSetTheVariablesCorrectly is still set to ''No'', please make sure the vars are set correctly and then change it to ''Yes'''

The IF statement would be executed correctly if it would get as far as executing the batch. But it's not even compiling.

This is because you have a USE statement on a non-existent database. The documentation says:

USE is executed at both compile and execution time and takes effect immediately. Therefore, statements that appear in a batch after the USE statement are executed in the specified database.

A USE statement on a non-existent database would prevent compilation, this is irrespective of whether there is an IF around it, or a CREATE or RESTORE statement before it.

Obviously, if you can execute this as separate batches then do so instead.

However, you cannot have separate batches in a single stored procedure. Instead you need to use dynamic SQL.

Place all the code you want to execute on that database into a variable.
restore database [SomeDB]
FROM DISK = N'C:\Temp\SomeDB.bak'
WITH FILE = 1, NOUNLOAD,
STATS = 5;

DECLARE @sp_exec nvarchar(1000) = 'SomeDB.sys.sp_executesql';
DECLARE @sql nvarchar(max) = '
--do things

--etc etc etc
';

EXEC @sp_exec @sql;


Don't forget to parameterize properly if necessary.

StackExchange Database Administrators Q#316537, answer score: 8