SQL SERVER | Table-Valued Parameters – an easy way to marshal multiple rows of data from a client application to SQL

In this post we shall understand the best use of Table-Valued Parameters and how we can use them to marshal multiple rows to the database(SQL SERVER) from UI. In this post e shall understand the limitations of Table-Valued Parameters.

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

Sample Example – How we can use the Table Variable as INPUT parameters

In this example we create a MyTable Table and pass the table variable to insert data using stored procedure.

CREATE MyTable

--

CREATE TABLE Mytable
( 
	 Id INT
	,Vals Varchar(10) 
)
GO

--

CREATE type as Table

--

CREATE TYPE dbo.MyData AS TABLE  
( 
	 Id INT
	,Vals Varchar(10) 
)
GO

--

CREATE stored procedure with table variable as input parameter

--

CREATE PROC InsertDATA
(
	@tvp MyData READONLY 
)
AS
BEGIN
	INSERT INTO Mytable  
	SELECT Id,Vals FROM @tvp
END


--

Call stored procedure passing table valued parameter as input parameter

--

DECLARE @tv AS MyData
INSERT INTO @tv VALUES (1,'Pawan')
EXEC InsertDATA @tv
GO

--

Verify Data

--

SELECT * FROM Mytable
GO

OUTPUT

Id          Vals
----------- ----------
1           Pawan

(1 row affected)


--

Notes/Limitations

1. You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

2. You cannot pass table-valued parameters to CLR user-defined functions.

3. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.

4. SQL Server does not maintain statistics on table-valued parameters.

5. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows.

6. If you still want to modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

7. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

References

Add your thoughts in the comments section

Add a comment if you have any solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com