6

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'''
        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.

3
  • 1
    Is it possible somewhere in your code your variable @iHaveSetTheTwoImportantVariablesCorrectly is getting set to NULL?...because any comparisons with the regular equality/inequality operators will always evaluate to FALSE, regardless of the statement, by design. Instead the operators IS NULL or IS NOT NULL need to be explicitly used. E.g. IF (NULL = NULL) and IF (NULL <> NULL) both always evaluate to FALSE.
    – J.D.
    Sep 7 at 2:13
  • "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." that probably doesn't work the way you think it does, which is probably what is causing the issue. But you haven't shown that part, please show the code that does this. As you can see from this fiddle dbfiddle.uk/Ay48LvVE USE NonExistentDB definitely doesn't work. Your exact error message would help also. Sep 7 at 2:20
  • @J.D. Thanks, yes I'm aware of the issue with trying to compare NULL to a value, and no it's not getting set to NULL anywhere. I'm pretty sure @Charlieface is right.
    – Jlanger
    Sep 7 at 2:45

1 Answer 1

8

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.

6
  • I think you've hit the nail on the head, I'll test this
    – Jlanger
    Sep 7 at 2:41
  • I agree with the diagnosis, but if this is a script rather than a stored procedure, you can create a new batch by inserting GO before you use the restored database. The first batch up to the GO will be compiled and executed, then the second batch (for which the restored database is now valid) will be compiled and executed.
    – grahamj42
    Sep 7 at 17:20
  • 1
    Yes @grahamj42 that is likely a good answer for most cases. In my case I have a bunch of control variables defined at the top of the script, and GO would limit their scope to the first batch only, whereas part of the usefulness of this script is in only having to edit the variables at the top before running it. I'll reference your comment in my update because it is a good point that others should see when coming here.
    – Jlanger
    Sep 7 at 20:55
  • @Jlanger I have occasionally used a temp table to pass parameters between batches.
    – grahamj42
    Sep 8 at 11:00
  • @grahamj42 that is something I've never considered, thanks for mentioning it
    – Jlanger
    Sep 8 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.