Passing multiple/dynamic values to Stored Procedures & Functions | Part 4 – by using TVP
This is the last fourth part of this series, in previous posts we talked about passing multiple values by following approaches: CSV, XML, #table. Here we will use a new feature introduced in SQL Server 2008, i.e. TVP (Table Valued Parameters).
As per MS BOL, TVPs are declared by using user-defined table types. We can use TVPs to send multiple rows of data to Stored Procedure or Functions, without creating a temporary table or many parameters. TVPs are passed by reference to the routines thus avoiding copy of the input data.
Let’s check how we can make use of this new feature (TVP):
-- First create a User-Defined Table type with a column that will store multiple values as multiple records: CREATE TYPE dbo.tvpNamesList AS TABLE ( Name NVARCHAR(100) NOT NULL, PRIMARY KEY (Name) ) GO -- Create the SP and use the User-Defined Table type created above and declare it as a parameter: CREATE PROCEDURE uspGetPersonDetailsTVP ( @tvpNames tvpNamesList READONLY ) AS BEGIN SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate FROM [Person].[Person] PER WHERE EXISTS (SELECT Name FROM @tvpNames tmp WHERE tmp.Name = PER.FirstName) ORDER BY FirstName, LastName END GO -- Now, create a Table Variable of type created above: DECLARE @tblPersons AS tvpNamesList INSERT INTO @tblPersons SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names) -- Pass this table variable as parameter to the SP: EXEC uspGetPersonDetailsTVP @tblPersons GO -- Check the output, objective achieved 🙂 -- Final Cleanup DROP PROCEDURE uspGetPersonDetailsTVP GO
So, we saw how we can use TVPs with Stored Procedures, similar to this they are used with UDFs.
TVPs are a great way to pass array of values as a single parameter to SPs and UDFs. There is lot of know and understand about TVP, their benefits and usage, check this [link].