3.13 Output Parameters
The stored procedure is passing data back to the calling application in two ways: a result set and a return code. There is a third way of passing data back: the use of output parameters.
The application development team have requested that the procedure send back an informative error message in the event of an validation error.
Requirement: If there are no products meeting the supplied criteria, an error is returned to the application with an informative error message.
Look at the changes to listing. The output parameter @vchMessage is declared using the keyword OUTPUT. The variable is populated along with the return code. Nothing else has to be done with the variable other than populate it - SQL Server will return its contents to the calling process.
alter procedure get_product
(
@idtStartDate datetime = Null,
@idtEndDate datetime = Null,
@vchMessage varchar(60) OUTPUT
)
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,
@vchMessage = 'No products found'
return @intRC
The calling process has to capture the output parameter. The next listing shows a stored procedure call using the named parameter. The calling process must also use the keyword OUTPUT to get the data.
declare @rc int, @msg varchar(60)
exec @rc = get_product
@idtStartDate = '1 Jan 2020',
@vchMessage = @msg OUTPUT
print 'The error message is ' + @msg
This is the output:
product_id product_name start_date
The error message is No products found