Table Valued Parameters

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

-- 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

*/

               

-- 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

*/

-- Cleanup

DROP FUNCTION tvfGroupByColor

DROP PROC uspMinMaxPriceByColor

DROP TYPE dbo.tpProdInfo

GO