Syntax error stops the execution of script!!!
Hello everybody!
There is something I still not understand in the way the DB engine works.
I have the following query, which purposefully contains an error:
Code:
declare @mystr nvarchar(3); set @mystr = 'C01'; declare @myint int; set @myint = @mystr;
Obviously, you cannot assign the string 'C01' to variable of type int! This is a syntax error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'C01' to a column of data type int.
If I embed that query in a string variable and call the sp_executesql procedure to process it or just use the EXEC statement, the error will be raised but the execution of query will stop neat, so that I won't be able to test the result of the call.
Here is my script:
Code:
declare @myquery nvarchar(4000);
set @myquery = 'declare @mystr nvarchar(3); set @mystr = ''C01''; declare @myint int; set @myint = @mystr;';
exec sp_executesql @myquery;
if @@ERROR <> 0
begin
print 'failed.'
end
else
begin
print 'done';
end
For instance, if you run this script in query analyzer, no message gets printed at all in query analyzer
This seems to happen only with syntax errors.
How can I avoid this situation????
Thanks a lot.