Tuesday, January 27, 2015

How to design table-valued parameters ?

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

Source : sqlusa

No comments:

Post a Comment