How does SET NOEXEC ON work?

Question

I have the following code:

-- start of code
set noexec off

declare @requiredVersion int
declare @currentVersion int

set @requiredVersion = 5
set @currentVersion = 4

if (@currentVersion < @requiredVersion)
begin
    print 'Please update your DB to version 5 before running this script.'
    set noexec on
end

go
-- print 'Dummy'
insert into tblFooBar(name) values ('AAA')
go

set noexec off
-- end of code

Please note that table "tblfoobar" does not exists in the database. When I run this code, the message comes up:

Please update your DB to version 5 before running this script.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblFooBar'.

I was expecting that setting up noexec to ON may not give the "Msg 208" part of the message.

Then again "set noexec on" compiles the code, does not execute it. Trying to insert something into a table that does not exists is a compile-time error - I am guessing. If that is the case, then the error about "missing object" should come up.

Now let me tell you the strange behaviour I have observed. If I remove the comment from the line "-- Print 'dummy'"

-- start of code
set noexec off

declare @requiredVersion int
declare @currentVersion int

set @requiredVersion = 5
set @currentVersion = 4

if (@currentVersion < @requiredVersion)
begin
    print 'Please update your DB to version 5 before running this script.'
    set noexec on
end
go
print 'Dummy'
insert into tblFooBar(name) values ('AAA')
go

and execute the code, I only get the following message.

Please update your DB to version 5 before running this script.

This time there is no message about missing table.

Can someone please explain this behaviour to me? Thanks.

Solution 1

I wouldn't have expected it to be valid to use "set noexec" within a conditional like you've done, but in fact it does seem to be valid.

The behavior seems to be related to the fact that it's a DDL statement. If you replace your insert with a print statement or simple select query, the script works as expected.

By the way, if I want to do something like this, I use the following approach:

    create proc tmproc_foobar as
       CREATE TABLE tblFooBar( name nvarchar(20) )
    go

    if exists ( select 1 from sysobjects where type = 'U' and name = 'tblFooBar' )
         exec tmproc_foobar
    go

    drop proc tmproc_foobar
    go

SQL Server allows you to create a stored procedure with an object that doesn't exist, so you can create the procedure, execute it conditionally, then drop it. This is the way I have a long upgrade script that conditionally adds any missing objects to my database.

You could adapt this perhaps. Then you don't need the noexec in the first place because you avoid the whole issue of invalid objects.

Solution 2

I have checked above code and it works fine. It doesn't give any error message for missing object.

set noexec off

declare @requiredVersion int
declare @currentVersion int

set @requiredVersion = 5
set @currentVersion = 4

if (@currentVersion < @requiredVersion)
begin
    print 'Please update your DB to version 5 before running this script.'
    set noexec on--> here we are setting NOEXEC on
end

go
-- print 'Dummy'
insert into tblFooBar(name) values ('AAA')
go

set noexec off
-- end of code

Since NOEXEC is on hence the below code will be compiled not gets executed, hence it will not through any error message.

Comments

[AhmedHuq]

If I replace INSERT with SELECT and comment PRINT stmt, I get the first error message (missing object). If I keep the PRINT statement, I get the second error message (nothing about missing object). The script does not work as expected (by me) if I replace the INSERT with a SELECT from tblfoobar.

[AhmedHuq]

It seems to work OK now! It was not working 5 years ago, and I think some of the comments by other users confirmed it.