Microsoft KB Archive/64842

{|
 * width="100%"|

INF: Global Variable @@error Checks for Successful Completion

 * }

Q64842

-

The information in this article applies to:


 * Microsoft Windows NT Server versions 3.1, 3.5, 3.51, 3.51 SP2, 3.51 SP5, 4.0, 4.0 SP6, 4.0 SP6a, 4.0 SP4
 * Microsoft Windows NT Workstation versions 3.1, 3.5, 3.51, 3.51 SP2, 3.51 SP5, 4.0, 4.0 SP6, 4.0 SP6a, 4.0 SP4
 * Microsoft SQL Server version 4.2x

-

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 &quot;select *&quot; 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:   )'''

The resulting set contains all rows returned from the &quot;titles&quot; table and all rows returned from the &quot;roysched&quot; 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 &quot;seleect&quot;. 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 &quot;0&quot; (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 &quot;error&quot;           >>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 &quot;1&quot;, 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 &quot;error&quot;           >>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 query words: Transact-SQL Windows NT

Keywords : kbprogramming kbOSWinNT310 kbOSWinNT350 kbOSWinNT351 kbOSWinNT400 kbOSWinNT400sp1 kbOSWinNT400sp2 kbOSWinNT400sp3 kbOSWinNT400sp4 _IK kbOSWinNT400sp6 kbOSWinNT400sp5 kbOSWinNT400sp6a kbSQLServ2000

Issue type :

Technology : kbWinNTsearch kbWinNTWsearch kbWinNTW400 kbWinNTW400search kbWinNT351xsearch kbWinNT350xsearch kbWinNT400xsearch kbWinNTW350 kbWinNTW350xsearch kbWinNTW351xsearch kbWinNTW351 kbWinNTW400sp4 kbWinNTW310 kbWinNTSsearch kbWinNTS400sp6 kbWinNTS400sp4 kbWinNTS400xsearch kbWinNTS400 kbWinNTS351 kbWinNTS350 kbWinNTS310 kbWinNTS351xsearch kbWinNTS350xsearch kbWinNTS310xsearch kbWinNTS351sp2 kbWinNTS351sp5 kbSQLServSearch kbAudDeveloper kbWinNT310xSearch kbWinNTW310Search kbWinNTW351sp5 kbWinNTW400sp6 kbWinNTW351SP2 kbWinNTW400SP6a kbSQLServ420