Microsoft® SQL Server® 2012 Bible

446

Part IV: Programming with T-SQL in SQL Server

5. The stored procedure completes and execution is passed back to SSMS. The value of
   the output parameter is received by the local variable @CurrencyCodeOutput.


6. The PRINT command sends the value of the local variable to the Messages tab of
   SSMS.
   This is the stored procedure:

USE AdventureWorks2012USE AdventureWorks2012

GO

CREATE PROCEDURE Sales.uspGetCurrencyName

@CurrencyCode char(3)

,@CurrencyName varchar(50) OUTPUT

AS

SELECT @CurrencyName = Name

FROM Sales.Currency

WHERE CurrencyCode = @CurrencyCode;

GO

This is the calling batch within SSMS:

DECLARE @CurrencyNameOutput varchar(50);

EXECUTE Sales.uspGetCurrencyName

@CurrencyCode = 'USD',

@CurrencyName = @CurrencyNameOutput OUTPUT;

PRINT @CurrencyNameOutput;

GO

Using the RETURN Command

A RETURN command unconditionally terminates the procedure and returns an integer value

to the calling batch or application. Technically, a RETURN can be used with any batch pro-

cess, but it can return a value only from a stored procedure or a function. The most com-

mon use of a RETURN command is to report success or failure of the process with a single

integer value. Common convention is to assign 0 to success and some other value to failure.

However, RETURN can be used by a calling application for any purpose that suits an

integer-based return code. The important consideration is that the database and

application is consistent in the use of RETURN.

If the RETURN code needs to be captured, the syntax for executing the stored procedure is

slightly different than what has been seen so far in the examples.

EXECUTE @LocalVariable = schema.uspStoredProcedureName;

c17.indd 446c17.indd 446 7/30/2012 5:39:36 PM7/30/2012 5:39:36 PM

http://www.it-ebooks.info