OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.