|
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.
-- 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
------------
Related articles:
Table-Valued Parameters (Database Engine)
Table-Valued Parameters in SQL Server 2008 (ADO.NET)
Passing lists to SQL Server 2005 with XML Parameters
|
