Stored procedures return a status value that indicates that they completed successfully or states the reasons for failure. This value can be stored in a variable when a procedure is called and used in future Transact-SQL statements. SQL Server - defined return status values for failure range from -1 through -99; users can define their own return status values outside this range.
Stored procedures can also return information to the caller through return parameters. Parameters designated as return parameters in the CREATE PROCEDURE and EXECUTE statements return the parameter values back to the calling procedure. The caller can then use conditional statements to check the returned value.
Return status values and return parameters allow you to "modularize" your stored procedures. A set of SQL statements used by several stored procedures can be created as a single procedure that returns its execution status or the values of its parameters to the calling procedure. For example, many of the SQL Server - supplied system procedures execute a procedure that verifies that certain parameters are valid identifiers.
Remote procedure calls (stored procedures run on a remote SQL Server) also return both types of information. All of the following examples in this section could be executed remotely if the syntax of the execute statement included the server, database, and owner names as well as the procedure name.