Coding Issues

To execute a stored procedure, you fully qualify the procedure name, including the server name and database name where the procedure is located. In this example, the batch executes the stored procedure order_ins_rev in database sales on the server national.

declare @ret int
execute @ret = national.sales..order_ins_rev 17, 225
if @ret != 0
   raiserror (“error in order insert”, 16, 1)
else
   print “order successfully entered”

Stored procedure result sets from remote procedures are ordinarily sent directly to the client connection. It is possible to redirect those results into a table on the local server with the INSERT … EXECUTE approach we looked at earlier in this section.

Transactions

Ordinary transactions do not affect remote procedures. Work performed in a stored procedure will not be rolled back as part of a transaction rollback:

declare @ret int
begin tran
insert local_table …
execute @ret = remote_server.remote_database..insertproc
update local_table …
if @@error <> 0
begin
   rollback tran              -- does not rollback the remote procedure
return
end
commit tran

---

NOTE:  Use distributed transactions to manage multiserver work with remote procedure.

---

Summary

The specific points in this chapter identify important coding standards you need to consider to build reliable procedures. Here are some broad guidelines for procedure programming. Use return status codes to make certain that procedures are ending properly. Pay close attention to error handling, within the procedure, in the batch and in the application calling the procedure. Carefully test transactional operations in procedures.

Q&A

Q How do applications retrieve information about errors and stored procedure return status?

A SQL Server returns error and return status information to the application in the same network data packets as result sets and other communications. The interface software (db-library or ODBC or some other system) works through the network data packets. Errors and messages are filtered out of that data stream and forwarded to the error- and message-handling routines your application designates. (The difficult part about dealing with errors is developing a method of communicating error information between the generic error and message routines and the part of the application that was running when the error was generated.)

Return status is easier to retrieve. The server includes procedure return status in the same data stream as result sets and errors. The return status is retrieved within the same procedure as the query result, so the coordination of error checking and application behavior is far simpler than using error messages.

Q Procedures seem to require a lot of work, compared to ordinary SQL. Do their benefits outweigh the work required?

A Procedures provide performance benefits, including reduced network traffic and stored optimizations. They also provide a method of storing units of well-tested, solid SQL code that perform necessary functions. The fact is that much of the error-checking and special handling required in stored procedures would have to be performed by your application otherwise. So, yes, on the whole, stored procedures do provide an overall benefit.

Q Should I trap specific errors within stored procedures?

A Not usually. Any non-zero error is a warning that something went wrong, causing a process to fail. (Don’t use the RAISERROR method of communication with a non-zero severity yourself unless something failed.) Your application can determine what the error was and how to treat it. In the procedure, you should probably just clean up the mess and exit.

Q When I find an error inside my procedure, @@error is always 0 inside the error trap. What’s going on?

A You’ve written the error trap incorrectly. Your code probably looks like this:

declare @err int, @rcount int
                -- variables to store error and rowcount
update titles   -- statement that causes the error to occur
set title_id = “RR1234”
select @err = @@error, @rcount = @@rowcount
if (@err != 0) or (@rcount = 0)
                -- check the error and rowcount
begin
   raiserror (“Error in update. Error no is %d, rowcount is %d”,
     0, 1, @err, @rcount)
   return
end
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint ‘UPKCL_titleidind’:
Attempt to insert duplicate key in object ‘titles’.
Command has been aborted.
Error in update. Error no is 0, rowcount is 0

The first four lines of the output are generated by SQL Server. The error number is 2627 , and the message provides more detailed information. The last line of output is from our RAISERROR message. Notice that the value of the error that was substituted is 0. The global variable, @@error , reports the error code from the last statement executed . If the statement succeeds, @@error is 0; any failure results in a non-zero error code. In this case, the error code reported in our RAISERROR statement is the status of the if @@error = 0 statement, which worked beautifully!

If you need to reuse the value of @@error , you must store it in a variable as soon as it is available (This is also true of @@rowcount .) Here’s how to capture and report @@error :

Msg  2627,  Level  14,  State  1
Violation  of  PRIMARY  KEY  constraint  'UPKCL_titleidind':
Attempt  to  insert  duplicate  key  in  object  'titles'.
Command  has  been  aborted.
Error  in  update. Error  no  is  0,  rowcount  is  0

Msg  2627,  Level  14,  State  1
Violation  of  PRIMARY  KEY  constraint  'UPKCL_titleidind':
Attempt  to  insert  duplicate  key  in  object  'titles'.
Command  has  been  aborted.
Error  in  update. Error  no  is  2627,  rowcount  is  0

The correct error number was reported.