How to use table variable in dynamic sql?

I came across a scenario while working with user-defined-table-types where I was need to query from table variable using dynamic SQL. I feel it worth sharing here in case someone might need in future.

I have created a user-defined table-type:

CREATE TYPE [dbo].[UDTT_Items] AS TABLE(    
    [ItemId] int identity(1, 1),
    [ItemCode] [varchar](10) NULL,
    [ItemName] [varchar](255) NULL, 
    [StockQty] decimal(18,3 ) NULL, 
    PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

In my stored procedure I declared a table variable for this UDTT:

declare @tblItems UDTT_Items

Here is how we normally use select statement on table variable.

select * from @tblItems

The problem comes when I tried to put this table variable in dynamic SQL. For example, if I try to run the above select statement from execute clause:

EXECUTE SP_EXECUTESQL N'select * from @tblItems'

It gives me the error message:

Must declare the table variable "@tblItems".

Fortuntely there is work around available by EXECUTE SP_EXECUTESQL. You can pass the table variable as a read-only parameter to dynamic SQL. So the following dynamic sql will work and run the given query as exptected.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * FROM @tblItems;';
EXECUTE SP_EXECUTESQL @SQL,N'@tblItems UDTT_Items READONLY',@tblItems;

Here I put my query in @SQL variable, and when calling EXECUTE SP_EXECUTESQL, we have to first define our parameters that need to be passed as second argument, and the actual table variable(i.e. parameter value) in third argument.

MS SQL Server - Show Query Execution Time

If you are using MS SQL Server 2008 or higher version, you can use the setting STATISTICS TIME. It will display the execution or run time in separate messages tab.

Setting this option ON will display the number of milliseconds required for complete statement cycle, i.e. parse, compile, and execute.

For example, following query will display order count for each customer who get registered in the month of August 2018.

select c.FirstName, c.LastName, c.RegistrationDate, Count(1) as OrderCount
from dbo.Customers c
 inner join dbo.Orders o on o.OrderId = c.OrderId
where 
 c.RegistrationDate between '1 August 2018' and '31 August 2018'
group by c.FirstName, c.LastName, c.RegistrationDate

Here is the sample output from messages tab, showing the execution time for above statement.

(200 rows affected)

 SQL Server Execution Times:
   CPU time = 1640 ms,  elapsed time = 2259 ms.
   

References:

• Microsoft - SET STATISTICS TIME (Transact-SQL)