0

Avoid TSQL Table Valued Parameter for Multiple SP Input Parameter

Emeka OkekeDec 16, 2019

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.

TVP Definition

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);

TVP Usage

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

Pros of TVP

Honestly there is only one pro of TVP that I can think of and it is:

  • Provides convenient way of passing multiple input parameter to stored procedures

Cons of TVP

Here are the cons of table valued parameters:

  • It cannot be altered after creation
  • If altering is a necessity, it must be dropped first before any new column could be added or dropped
  • Also because they must be dropped first, this might cause issues with SPs and functions that are currently using it in live production
  • It takes up unnecessary space in the database which adds to the database maintenance cost

Alternatives to TVP

Instead of using table valued parameter, there are two much better alternatives that due to inexperience, a lot of database developers often overlook.

XML Datatype Methods

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:

  • .nodes: This datatype converts an xml document into relational entities. Hence it can be used to convert an XML document into a relational two-dimensional matrix table
  • .value: The value datatype is used to retrieve a TSQL typed value from an XML document

XML Usage

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)[1]', 'INT') AS ID,
	Tbl.Col.value('(Location)[1]', 'CHAR (10)') AS Location
	FROM @TbXML.nodes('/row') Tbl(Col)

END

JSON

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.

JSON Usage

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

Summary

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.

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia