Table 18.1 describes the common errors you will encounter when you run a stored procedure that updates data.

The first set of errors in the table (errors with “Yes” under Proc Error Trap) is trappable in the procedure. These are the easiest errors to catch because the error trap knows and understands the current context and is written for the specific instruction where the error occurred. When these errors are trapped, simply return a non-zero status from the procedure to let the batch know a problem was encountered.

The second group of errors (those with “No” in the Proc Error Trap, but with a non-zero return status trappable in the batch) causes the procedure to quit, but returns a meaningful return status code to the batch. Data errors (201) require you to look at your application program for causes. Missing tables (208) are often caused by failed database maintenance routines.

The third group of errors terminates both the procedure and the batch. These must be trapped in your application, which should check the return status using the db-library functions, dbhasretstat and dbretstatus . You can also use the application’s error and message handlers (dberrhandle and dbmsghandle ).

The fourth group of errors terminates the procedure and batch but does not return a negative status value. For these, your only choice is to use the error and message handlers. (Tomorrow, you will learn how to write triggers. At that time, I’ll remind you to raise an error if you roll back a trigger. As you can see, the server does not warn of trigger failure in any other way.)

Finally, when there is a data overflow during parameter assignment, the procedure and batch are terminated and the user receives a message (not an error) that there was an overflow. You receive no other warning.

---

NOTE:  It’s pretty complicated to build robust systems that monitor all possible errors in a database. Be careful to watch the return status from both the batch and the application in case of abnormal termination. Use meaningful return status values in your own applications and raise errors wherever they help you build a good connection between the server and the client or the user.

---

System Procedures

As you have learned T-SQL, you have used lots of stored procedures to get information about tables and columns, report on the status of databases, or learn about available indexes. There are also procedures that allow you to manage system logins, database user names, table and database characteristics, and the overall configuration of the server.

These procedures, shipped with SQL Server, are known as system procedures.

System procedures are stored procedures used to manage the system and interact with system tables.

Requirements of System Procedures

To write a system procedure, you must log in as sa and use the master database. The names of system procedures must start with sp_ . Here is a simple procedure that returns the names of columns for a table:

create proc sp_quickcolumns
   (@objname sysname)
as
if object_id(@objname) is null       -- object does not exist
begin
   raiserror (“%s not found”, 0, 1, @objname)
   return 99
end
select name
  from syscolumns
 where id = object_id(@objname)
 order by colid
return 0

System procedures usually refer to system tables. The SELECT query in the procedure retrieves the names of all columns in the table or view (or all parameters for a procedure).

System procedures also make frequent use of system functions. Notice how the test for object existence (object_id(@objname) ) was performed without the use of a SELECT statement. Without the system function, the condition would have required an EXISTS clause:

if exists (select * from sysobjects where name = @objname)

This method would require full SQL query processing, including optimization, compilation, and execution.

---

NOTE:  OK, I have no real information about how the condition is evaluated in

 if object_id(@objname) is null       -- object does not exist

What I can tell from looking at output from showplan and statistics io is that the condition is not included in the optimization plan and generates no measurable I/O.

---