Microsoft® SQL Server® 2012 Bible

450

Part IV: Programming with T-SQL in SQL Server

);

GO

--Create Procedure

CREATE PROCEDURE Sales.uspInsertNewCurrency

@CurrencyCode char(3)

AS

SELECT CurrencyCode, Name

FROM Sales.Currency

WHERE CurrencyCode = @CurrencyCode;

GO

--INSERT... EXECUTE into Sales.NewCurrency

INSERT Sales.NewCurrency(CurrencyCode, Name)

EXECUTE Sales.uspInsertNewCurrency @CurrencyCode = 'CAD';

--View the contents of Sales.NewCurrency

SELECT CurrencyCode, Name

FROM Sales.NewCurrency;

INSERT...EXECUTE can be used with a permanent table, a table variable, or a temporary

table.

Path and Scope of Returning Data

Any stored procedure has fi ve possible methods to return data (SELECT, OUTPUT param-

eters, RETURN, RAISERROR/THROW, and table population). Deciding which method is

right for a given stored procedure depends on the quantity and purpose of the data to be

returned and the scope of the method used to return the data. The return scope for the fi ve

methods is as follows:

■ (^) Selected record sets are passed to the calling stored procedure. If the calling
stored procedure consumes the result set (for example, INSERT... EXECUTE)
the result set ends there. If the calling stored procedure does not consume the
result set, it is passed up to the next calling stored procedure or client.
■ (^) RETURN values and OUTPUT parameters are all passed to local variables in the
immediate calling procedure or application within SQL Server.
■ (^) RAISERROR is passed to the calling stored procedure and continues to bubble up
until it is trapped by a TRY... CATCH or it reaches the client application.
If SQL Server Management Studio (SSMS) executes a batch that calls stored procedure A,
which then calls stored procedure B, procedure B can pass data back to procedure A or to
the client application in multiple ways, as illustrated in Figure 17-1.
c17.indd 450c17.indd 450 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM
http://www.it-ebooks.info