I have a temp table like
DECLARE @t TABLE (a BIGINT)
I want to do this
SELECT * FROM @t
but I need to do it by EXEC query, and this doesn't work
DECLARE @query AS NVARCHAR(100) = 'SELECT * FROM @t'
EXEC(@query)
how can I create a custom query to select the temp table?
Thanks.
This is because EXEC statement will execute the Statements in new Session. And the Table variables scope is fixed to the batch of statement.
Since you declared the Table Variable out side of the Session , you can't access the table variable in EXEC statement.
So you need to DECLARE ,INSERT, UPDATE, SELECT table variables in the Dynamic Code itself.
DECLARE @query NVARCHAR(MAX)='';
SELECT @query ='
DECLARE @t TABLE
(
a BIGINT
)'
SELECT @query += 'SELECT * FROM @t'
EXEC(@query)
Solution 2:
The another Solution is to create Global Temporary Table which we can create using ##. And Global Temporary tables scope is limited to Database all connections.
CREATE TABLE ##TABLE1
(
a BIGINT
)
DECLARE @query NVARCHAR(MAX)='';
SELECT @query += 'SELECT * FROM ##TABLE1'
EXEC(@query)
But be aware if another user execute the same Query, there might be conflict with the same name creation.
Try hash table as below:
DECLARE @tblTest AS Table
(
Name VARCHAR(50)
)
insert into @tblTest values('Sandip')
insert into @tblTest values('AAA')
IF OBJECT_ID('tempdb..#tblTest') IS NOT NULL
DROP TABLE #tblTest
SELECT * INTO #tblTest FROM @tblTest
DECLARE @query AS NVARCHAR(100) = 'SELECT * FROM #tblTest'
EXEC (@query)
Output:
CREATE TYPE MyTable AS TABLE
(
a BIGINT
);
GO
DECLARE @T AS MyTable;
EXEC sp_executesql
N'SELECT * FROM @T',
N'@T MyTable READONLY',
@T=@T
please try this
