Returning Values

In most of our stored procedures, we will either modify or return a set of data. You may also wish to return some value that signifies a successful or failure. In the remaining stored procedures, we will perform some business functionality, where we may return values calculated during that time.

There are different ways to return this sort of information and it depends on the number of values we wish to return and also how we wish to handle these within either other stored procedures or calling programs. Let's look at each method now.

RETURN

This is either implicit or explicit within a stored procedure, and will execute as the last statement of a stored procedure. This statement terminates the stored procedure immediately, with no further statements executing after this statement.

If RETURN is specified, then a value of 0 is returned, however, we can also return our own integer value. The normal practice is that a negative number returned indicates that an error has occurred. A positive number indicates some other reason, but it is often used to return the number of rows that have been returned or modified within a statement.

To demonstrate this, let's create a procedure that runs against the Categories table in northwind and returns the rows from the table and the number of rows found.

     CREATE PROCEDURE sel_Categories     AS     BEGIN       DECLARE @Rc INT       SELECT CategoryName, Description       FROM Categories       SET @Rc = @@ROWCOUNT       RETURN @Rc       -- Any statement here will not execute     END     GO 

If you are wish to execute this statement in the Query Analyzer, then you need to define it as:

     DECLARE @RcRet INT     EXEC @RcRet = sel_Categories     SELECT @RcRet "No. Rows" 

It is necessary to have a local variable defined for the return value to be placed at the end of the calling procedure. The final line simply prints out the return value.

Note

If you return the value from a local variable, the setting of that local variable cannot be NULL, and has to be a valid integer number.

The next section examines returning more than one value or non-integer values.

OUTPUT

Even if a RETURN statement is defined, we can supplement or replace this by output parameters. There is a limit of 2,100 to the number of parameters we can define. When we come close to this number, we should review what we are doing.

Defining an output parameter is similar to an input parameter – define the name of the variable prefixed with an @ sign, the data type, and the precision, where necessary, and then finally, the keyword OUTPUT.

After taking the stored procedure above and altering it slightly, we can see:

     CREATE PROCEDURE sel_CategoriesWithOutput @Rcnt INT OUTPUT     AS     BEGIN       SELECT CategoryName, Description       FROM Categories       SET @Rcnt = @@ROWCOUNT       RETURN 1       -- Any statement here will not execute     END     GO 

As we can see, we are still returning a value as well as the number of rows found in a parameter. To execute this stored procedure, we define the return code variable, as we did in the previous example, but also define the output parameter with the OUTPUT keyword:

     DECLARE @OutParm INT, @RetVal INT     EXEC @RetVal = sel_CategoriesWithOutput @OutParm OUTPUT     SELECT @OutParm "Output Parm", @RetVal "Return Value" 

Now we will look at the last method of retrieving information from a stored procedure.

Single Row of Data

Ignoring the fact that stored procedures will return data from a SELECT statement listing information that meets a specified set of criteria, it is also possible to return either a second set of records or a single row rowset that is the only information returned, neither of which can be attributed directly to a table. This is an unusual scenario and will only come about if you felt that the number of output parameters is excessive or if you are returning a set of information that will be simpler to use in the calling object as a tabular set of information rather than a set of values. A good example of this would be if the stored procedure was called as part of an Excel spreadsheet.

When an Error Occurs

Working with errors brings up several other considerations besides simply returning a value, which we will examine in detail in the next chapter.

Other Issues with Returning Values

It is possible to return a table as a data type from a stored procedure. A table data type is a special data type, and is simply a method to hold a temporary table in a variable rather than tempdb or your own database.

This data type should not be passed as the output parameter of a stored procedure, since the calling routine may not be able to handle that data type. If you want to return a rowset, it will be better to simply do that using the SELECT statement.

Ensure that the data types you are retrieving from a data source such as a table match the data types of the input and output parameters – there will definitely be problems when the data types don't match. For instance, a data type definition of CHAR(4) will always be 4 characters long. So if you placed ‘TX’ into a variable it will be ‘TX ‘ (two spaces appended). However, it will always be stored as ‘TX’ with no spaces in a VARCHAR. Here you will only be sending two extra characters with CHAR, but if you are doing this many thousands of times, it becomes evident that VARCHAR be a better option.

Use VARCHAR wherever you can, and INT instead of BIGINT, to reduce the overhead of passing unnecessary data. Also send at least the minimum number of characters so that potential errors with a data type that is too small are not created.