ID Number: Q64842
1.10 1.11 4.20
OS/2
Summary:
This article discusses how to check to see if a Transact-SQL command
completed successfully when executed within a command file.
The global variable @@error can be tested to check for the successful
completion of a Transact-SQL command within a command file using the
control-of-flow language.
More Information:
The two examples included below illustrate the use of the global
variable @@error. The first example, ERR1.CMD, will execute the
Transact-SQL "select *" command on the table names supplied from the
command line. For example, to execute this command file, type the
following at the command prompt:
err1 titles roysched
(Format: <batch_file_name> <table_name> <table_name>)
The resulting set contains all rows returned from the "titles" table
and all rows returned from the "roysched" table. Both tables are
located in the sample pubs database.
The second example, ERR2.CMD, will attempt to execute the first
SELECT * command on the table provided by the first table_name
parameter, but the global variable @@error will have been set to 1,
indicating that an error has been encountered. Processing of the
control-of-flow language will stop, and no records will be retrieved
for either table. The error is encountered because there is a syntax
error in the word SELECT. Instead of using the proper spelling of
SELECT, it is misspelled as "seleect". To execute this batch file,
type the following at the command prompt:
err2 titles roysched
As indicated above, the resulting set will be empty because an error
was encountered.
Example 1: ERR1.CMD
-------------------
The global variable @@error will have the value of "0" (zero),
indicating that the first SELECT command completed successfully;
thus, all records will be retrieved from both tables.
@echo off
set tb1=%1
set tb2=%2
echo /* Selecting records for tables: %tb1% and %tb2% */
echo use pubs >isql.in
echo go >>isql.in
echo begin >>isql.in
echo select * >>isql.in
echo from %tb1% >>isql.in
echo end >>isql.in
echo go >>isql.in
echo begin >>isql.in
echo If @@error !=0 >>isql.in
echo print "error" >>isql.in
echo else >>isql.in
echo if @@error = 0 >>isql.in
echo select * from %tb2% >>isql.in
echo end >>isql.in
echo go >>isql.in
isql /Usa /P /n /i isql.in
Example 2: ERR2.CMD
-------------------
The global variable @@error will have the value of "1", indicating
that an error was encountered in the first SELECT command (there is
a syntax error in the word SELECT in the first SELECT command);
therefore, the second SELECT will not be executed.
@echo off
set tb1=%1
set tb2=%2
echo /* Selecting records for tables: %tb1% and %tb2% */
echo use pubs >isql.in
echo go >>isql.in
echo begin >>isql.in
echo seleect * >>isql.in
echo from %tb1% >>isql.in
echo end >>isql.in
echo go >>isql.in
echo begin >>isql.in
echo If @@error !=0 >>isql.in
echo print "error" >>isql.in
echo else >>isql.in
echo if @@error = 0 >>isql.in
echo select * from %tb2% >>isql.in
echo end >>isql.in
echo go >>isql.in
isql /Usa /P /n /i isql.in
Additional reference words: Transact-SQL