Microsoft® SQL Server® 2012 Bible

447

Chapter 17: Developing Stored Procedures

17

The following example alters the stored procedure created to illustrate output parameters

and checks to see whether the output parameter is NULL. If so, it assigns a RETURN code of

1 ; if not, a RETURN code of 0 is assigned.

USE AdventureWorks2012

GO

ALTER PROCEDURE Sales.uspGetCurrencyName

@CurrencyCode char(3)

,@CurrencyName varchar(50) OUTPUT

AS

SELECT @CurrencyName = Name

FROM Sales.Currency

WHERE CurrencyCode = @CurrencyCode;

IF @CurrencyName IS NULL

BEGIN

RETURN 1;

END;

ELSE

BEGIN

RETURN 0;

END;

GO

--Receive a RETURN code of 0

DECLARE @CurrencyNameOutput varchar(50);

DECLARE @ReturnCode int;

EXECUTE @ReturnCode = Sales.uspGetCurrencyName

@CurrencyCode = 'USD',

@CurrencyName = @CurrencyNameOutput OUTPUT;

PRINT @CurrencyNameOutput;

PRINT @ReturnCode;

GO

--Receive a RETURN code of 1

DECLARE @CurrencyNameOutput varchar(50);

DECLARE @ReturnCode int;

EXECUTE @ReturnCode = Sales.uspGetCurrencyName

@CurrencyCode = 'USA',

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

http://www.it-ebooks.info