This post will show you why you need to avoid TVPs or table valued parameters. It will show you the pros and cons of TVP and the better alternatives to TVP. TVP or table valued parameter is a TSQL custom table datatype that is used to create a table blueprint or template to be used by a table variable. The purpose of TVP is to pass multiple input parameters to a stored procedure. When used in a stored procedure, it must be declared as a READONLY parameter because it is immutable once created.
Below is how a TVP is defined:
CREATE TYPE [DBO].[TBLTYPE] AS TABLE ( [Id] INT NULL, [InitiativeId] INT NULL, [FID] CHAR (5) NULL, [Location] CHAR (10) NULL, [Response] BIT NULL, [LoginName] VARCHAR (25) NULL);
Then it is used as follows in a typical stored procedure:
CREATE PROCEDURE [dbo].[Proc_Name] @TbType dbo.TBLTYPE READONLY AS BEGIN SELECT ID, Location FROM @TbType END
Honestly there is only one pro of TVP that I can think of and it is:
Here are the cons of table valued parameters:
Instead of using table valued parameter, there are two much better alternatives that due to inexperience, a lot of database developers often overlook.
XML had long been introduced to TSQL since 2005. It is a very powerful feature of TSQL that I don't see a lot of database developers using. The TSQL XML datatype is used to hold xml documents in variables and in table columns. This post is not about XML datatype so I will leave the details for another post. However, as an alternative to TVP, I will talk about the two key xml datatype methods which are:
Below is how XML can be used to pass multiple input parameter to a TSQL stored procedure. You first of declare an XML variable that holds the multiple input values:
DECLARE @MultipleParams XML=(SELECT ID,Location FROM dbo.TBL FOR XML PATH)
Then you pass and use it in a stored procedure:
CREATE PROCEDURE [dbo].[Proc_Name] @TbXML XML AS BEGIN SELECT Tbl.Col.value('(ID)', 'INT') AS ID, Tbl.Col.value('(Location)', 'CHAR (10)') AS Location FROM @TbXML.nodes('/row') Tbl(Col) END
TSQL JSON document storage and manipulation feature was introduced in SQL Server 2017. If you are using this version of SQL Server, then you should use JSON instead of XML. We will go into details of JSON in another post.
Below is how JSON can be used to pass multiple input parameter to a TSQL stored procedure. First of all you create the JSON document with the multiple input parameters:
DECLARE @MultipleParams VARCHAR(500)=(SELECT ID,Location FROM dbo.TBL FOR JSON PATH)
Then you can use it in a stored procedure as follows:
CREATE PROCEDURE [dbo].[Proc_Name] @TbJSON VARCHAR(500) AS BEGIN SELECT id,location FROM OPENJSON(@TbJSON) WITH(id INT '$.ID',location CHAR(10) '$.Location') END
Note that JSON is case sensitive. This means
ID is not the same as
JSON or XML datatypes should be used to move around data for example to pass multiple values to a stored procedure instead of Table Valued Parameter. This is because TVPs cannot be altered once they are created. To alter or modify them, they must be dropped first, and recreated with the new changes. Also JSON and XML are supported by virtually all programming languages including TSQL.
The requirement to drop them first before making any changes might cause issues with SPs and functions that are currently using them in live production. Additionally they take up space in the database and add to maintenance cost.
More from @sqldibia