How to design table-valued parameters?

Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the design and use of table-valued parameters in stored procedures and functions(UDFs).

-- SQL Server Table-Valued Parameters of functions and stored procedures

USE AdventureWorks2008;

GO

-- Create new table type

CREATE TYPE dbo.tpProdInfo AS TABLE(ProdID INT, ProdNbr char(12),

                                    ListPrice money, Color char(16));

GO

-- Create table-valued function(UDF) with table-valued parameter

CREATE FUNCTION tvfGroupByColor (@Input dbo.tpProdInfo READONLY)

RETURNS @Result TABLE (Color char(16), AvgListPrice money)

AS

BEGIN

  INSERT @Result

  SELECT Color, avg(ListPrice) FROM @Input

  GROUP BY Color

  RETURN

END

GO

-- Create stored procedure with table-valued parameter

CREATE PROCEDURE uspMinMaxPriceByColor @Input dbo.tpProdInfo READONLY

AS

BEGIN

  SELECT Color, MinPrice=min(ListPrice), MaxPrice=MAX(ListPrice)

  FROM @Input

  GROUP BY Color

  ORDER BY MaxPrice DESC

END

GO

-- Test TVF with table-valued parameter

DECLARE @PriceDetail dbo.tpProdInfo

INSERT @PriceDetail SELECT ProductID, ProductNumber, ListPrice, Color

                    FROM Production.Product WHERE Color is not null

SELECT * FROM tvfGroupByColor (@PriceDetail)

ORDER BY AvgListPrice DESC

GO

/*

Color             AvgListPrice

Red               1401.95

Yellow            959.0913

Blue              923.6792

Silver            850.3053

Black             725.121

Grey              125.00

Silver/Black      64.0185

Multi             59.865

White             9.245

*/

-- Test stored procedure with table-valued parameter

DECLARE @PriceDetail dbo.tpProdInfo

INSERT @PriceDetail SELECT ProductID, ProductNumber, ListPrice, Color

                    FROM Production.Product WHERE Color is not null

EXEC uspMinMaxPriceByColor @PriceDetail

GO

/*

Color             MinPrice    MaxPrice

Red               34.99       3578.27

Silver            0.00        3399.99

Black             0.00        3374.99

Blue              34.99       2384.07

Yellow            53.99       2384.07

Grey              125.00      125.00

Multi             8.99        89.99

Silver/Black      40.49       80.99

White             8.99        9.50

*/

-- Cleanup

DROP FUNCTION tvfGroupByColor

DROP PROC uspMinMaxPriceByColor

DROP TYPE dbo.tpProdInfo

GO

------------