datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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

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

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

 

Exam Prep 70-461
Exam 70-461