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.
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''' return end use [DatabaseThatExists] set SomeColumn = 'a value' where SomeColumn = 'another value' use [master] sp_configure 'clr enabled', 1 reconfigure create asymmetric key AKey from executable file = 'C:\Path\to\executable.exe' create login ##UserThatDoesNotExist## from asymmetric key AKey grant unsafe assembly to ##UserThatDoesNotExist## use [master] restore database [SomeDB] FROM DISK = N'C:\Temp\SomeDB.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 use [SomeDB] --do things --etc etc etc
The code after the
if... return block is just an example of the kind of things being done, some of which will have errors prior to execution of earlier parts of the script.
TL;DR - The
if... return block in the above example is not being evaluated. The only situation I've found where it is evaluated is if all the code after it is removed.