Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
if (@currentVersion < @requiredVersion)
begin
print 'Please update your DB to version 5 before running this script.'
set noexec on
-- print 'Dummy'
insert into tblFooBar(name) values ('AAA')
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
print 'Dummy'
insert into tblFooBar(name) values ('AAA')
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.
–
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) )
if exists ( select 1 from sysobjects where type = 'U' and name = 'tblFooBar' )
exec tmproc_foobar
drop proc tmproc_foobar
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.
–
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
-- print 'Dummy'
insert into tblFooBar(name) values ('AAA')
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.
–
I encountered same issue, and got a way could solved this issue better.
Just put the compiled error content into a EXEC statement like this:
EXEC('
--Put the compiled error here
Thus you also saved a GO statement, and just replaced the single "'" with double "''" to put the code here.
This EXEC statement could avoid the complied error and reached the wishes you primarily want.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
