Five Ways to Communicate Between Stored Procedures and Client Programs

You can return information from a procedure to a calling program, a client application, or a user in any of five ways. It's important to understand what those return methods are for, and how they are different:

• SELECT is for data. Try to use it only to return data to the client application that's intended for processing, printing, or displaying onscreen.

• PRINT is for messages. Use it when you want to send a noncritical message to the user (not the programmer) about status, progress, or success.

• RAISERROR is for errors. Use it when you want to send a critical message to the user about a failed process or invalid choice. Notice that this statement changes the value of @@error. If the server has already raised an error, you might choose not to.

• Output parameters return a value to a program (not a user). They are not shipped immediately to the client program; instead, they are returned to the program that called a stored procedure.

• Return status tells the programmer whether a procedure worked or failed. Return status is only available to the program or procedure that calls a stored procedure. By convention, any non-zero return status means that a procedure failed.