SQL SERVER | How to use Table Variable in Dynamic SQL ? | SQL Interview Question

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.

In this post we shall understand how we can use the Table Variable Parameter in a dynamic SQL?

Answer – We need to use SP_EXECUTESQL

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

CREATE TestTableVariable table

--

CREATE TABLE TestTableVariable
(
	 Id Int
	,Vals VARCHAR(100)
)
GO

INSERT INTO TestTableVariable VALUES (1, 'Pawan') ,(2, 'Avtaar') 
GO

--

CREATE aNewType as Table

--

IF EXISTS (SELECT * FROM sys.types WHERE NAME = 'aNewType')
  DROP TYPE aNewType

CREATE TYPE aNewType AS TABLE (Id INT)
GO

--

CREATE stored procedure with table variable as input parameter

--

CREATE PROC MatchingData
(
	@tVariable aNewType READONLY
)
AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)= N' SELECT s.* FROM TestTableVariable s 
        WHERE EXISTS ( SELECT NULL FROM @tVariable t WHERE t.Id = s.Id ) '	
	DECLARE @defn nvarchar(500) = N' @tVariable aNewType READONLY'; 	
	Execute SP_EXECUTESQL @SQL , @defn , @tVariable
END

--

Call stored procedure passing table valued parameter as input parameter

--

DECLARE @tv AS aNewType
INSERT INTO @tv VALUES (1)
EXEC MatchingData @tv
GO

--

Verify Data for Id = 1 | Matching Data

--

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

(1 row affected)

--

Verify Data for Id = 2 | Matching Data

--

Id          Vals
----------- -------------------
2           Avtaar

(1 row affected)

--

Verify Data for Id = 3 | NON Matching Data

--

Id          Vals
----------- ----------

(0 rows affected)

--

Notes/Limitations of table-valued parameter

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

Related Post

Add your thoughts in the comments section. Thank you for reading.

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