TVP | SQL with Manoj

Pass an array of multiple values with a single Parameter in a Stored Procedure – SQL Server

July 10, 2015 3 comments

This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.

Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.

Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.

–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.

Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.

Thanks a lot, please provide your valuable comments and suggestions on this topic.

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check & Like my FB Page

Passing multiple/dynamic values to Stored Procedures & Functions | Part 4 – by using TVP

September 10, 2012 2 comments

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