Support of Table-Valued Parameters is another great new feature in SQL Server 2008.
I am sure that all of us had to make a stored procedure or function call in SQL Server many times. In the cases where many values should be passed as parameters it was a little bit difficult as we had to use some really long lines of code. Not any more with Table-Valued Parameters :)
The usual procedure for using TVPs is the following:
1. Create a user-defined table type that can be passed as a TVP to a function or stored procedure
Code Example:
--Create a table type
CREATE TYPE employee AS TABLE
(ID int,
NAME varchar(50))
GO
2. Create a stored procedure or function that uses a TVP
Code Example:
--Create a stored procedure that takes as a parameter a TVP
CREATE PROCEDURE showTVPValues(@TVParameter employee READONLY)
AS
SET NOCOUNT ON
SELECT * FROM @TVParameter
GO
3. Declare the table type variable
Code Example:
--Declare a variable that references the user-defined table type
DECLARE @TVP_Param AS employee;
4. Populate the table type variable with data
Code Example:
--Add data to the table variable
INSERT INTO @TVP_Param (ID, NAME)
SELECT [ID],[NAME]
FROM [table_name].[column_name];
5. Pass the table type variable as a parameter to the stored procedure
Code Example:
--Pass the table variable to the stored procedure
EXEC showTVPValues @TVP_Param;
GO
With this way you can pass tabular data to a function or stored procedure as a single parameter.
Labels: SQL Server, SQL Server 2008, SQL Server Development