Microsoft® SQL Server® 2012 Bible

444

Part IV: Programming with T-SQL in SQL Server

WHERE CurrencyRateDate = @CurrencyRateDate;

GO

To execute the stored procedure, a table-valued parameter needs to be declared and then

populated. Then, it can be assigned to the input parameter during stored procedure

execution.

DECLARE @CurrencyCodeTVP as CurrencyCodeListType

INSERT INTO @CurrencyCodeTVP(FromCurrencyCode, ToCurrencyCode)

VALUES

('USD', 'AUD'),

('USD', 'GBP'),

('USD', 'CAD'),

('USD', 'MXN');

EXECUTE Sales.uspGetCurrencyRatesUDT

@CurrencyCodeTable = @CurrencyCodeTVP,

@CurrencyRateDate = '2005-07-14';

All the preceding examples illustrate one key point about stored procedures. Passing a list

or a table into a stored procedure creates additional complexity and forethought to make

it work. Take the calling application into consideration, and if at all possible, discuss the

different options with the development team. What works well for one application might be

inelegant for another.

Returning Data from Stored Procedures

The previous section covered how to get data into a stored procedure. This section

addresses how to get the data back out. SQL Server provides fi ve means to return data

from a stored procedure. You can use any combination of these options in a single stored

procedure.

■ (^) Output parameters: Scalar data can be returned from a stored procedure with out-
put variables.
■ (^) RETURN: A single integer value can be returned from a stored procedure with a
RETURN statement.
■ (^) Result sets: A stored procedure can return data via one or more SELECT
statements.
■ (^) RAISERROR or THROW: Informational or error messages can be returned to the call-
ing application via RAISERROR or THROW.
■ (^) Table population: A table can be populated as part of stored procedure processing
and then queried after execution.
c17.indd 444c17.indd 444 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM
http://www.it-ebooks.info