3.12 Error Processing : the Return Code

What if there are no records found to match input criteria? On discussion with the client application developers it's decided that this is to be treated as an error.

Requirement: If there are no products meeting the supplied criteria, an error is returned to the application.

All stored procedures send a return code back to the calling process. Despite the fact that the current stored procedure does not reference a return code, SQL Server ensures that a value of 0 is returned to indicate success. To prove the point, we will change the way we are calling the stored procedure.

declare @rc int

 

exec @rc = get_product

 

print 'The return code from this stored procedure is ' + convert(varchar(8), @rc)

 

This is the output:

product_id product_name start_date
1 Blue Widget Jan 1 2007
3 Grey Widget Jan 1 2005

 

The return code from this stored procedure is 0

 

Any variable when first assigned is null, so clearly the stored procedure has placed a 0 value into @rc variable. It is good practice to assign a return code explicitly in all stored procedures, keeping the SQL Server convention of using 0 to denote success and a positive value for user-defined errors.

For our purposes, we will return a code of 101 to the calling application if there is an empty result set. The next listing shows our stored procedure re-write.

alter procedure get_product

(

@idtStartDate datetime = Null

)

as

declare @intRC int, @intCount int

 

select @intRC = 0

 

-- If the calling application has provided a date, use it
-- Otherwise, check the product start date is before the system date.
-- If no records are found, return an error

 

IF @idtStartDate is NOT NULL
  select product_id, product_name, start_date
  from product
  where start_date >= @idtStartDate

 

ELSE

  select product_id, product_name, start_date
  from product
  where start_date <= getdate()

select @intCount = @@rowcount

 

if @intCount = 0

  select @intRC = 101

 

return @intRC

 

We start the stored procedure by declaring two integer variables and assign a value of 0 to the return code variable. Right after the SELECT statement, the global @@rowcount variable is stored in a local variable. This global variable has a count of the rows affected by the most recent SQL statement. If the value is 0, the user-defined error number 101 is assigned to the return code variable.

The very last line uses the return keyword to send the status back explicitly.

Lets test our new version of the stored procedure by calling it with parameters that we know won't find any records.

declare @rc int

 

exec @rc = get_product '1 Jan 2020'

 

print 'The return code from this stored procedure is ' + convert(varchar(8), @rc)

This is the output:

product_id product_name start_date
     

 

The return code from this stored procedure is 101