Is it possible to use local table variables in a p

问题:

I have the following code:

DECLARE @temp_table_1 TABLE (id int identity(0, 1), col_1 varchar(50)), 
        @txtVar VARCHAR(MAX)

INSERT INTO @temp_table_1
  SELECT col_1 FROM table_1  -- This table_1 is a real table in the database.

Set @txtVar = 'SELECT * FROM @temp_table_1'

EXECUTE (@txtVar)

The error I get is

Declare variable @temp_table_1.

How can I fix this?

回答1:

Set @txtVar = 'SELECT * FROM myTable WHERE column_value=''' + @var1 + ''''

This article will help you get a basic ideas of dynamic sql.

EDIT
It is not possible to use table variables in a dynamic query.
You have to use temporary table or Use custom TABLE type.

Temporary table

 CREATE TABLE #temp_table_1 
  ( 
     id    INT IDENTITY(0, 1), 
     col_1 VARCHAR(50) 
  ) 

DECLARE @txtVar VARCHAR(MAX) 

INSERT INTO #temp_table_1 


SELECT col_1 
FROM   table_1 -- This table_1 is a real table in the database. 
SET @txtVar = 'SELECT * FROM  #temp_table_1' 

EXECUTE (@txtVar) 

DROP TABLE #temp_table_1 

Custom Table Type

CREATE TYPE DefaultTable AS TABLE (ID INT IDENTITY(0, 1), COL_1 VARCHAR(50))
GO

-- Fill a var of that type with some test data
DECLARE @MyTable DefaultTable
INSERT @MyTable 
SELECT col_1 FROM table_1 -- This table_1 is a real table in the database.

-- Now this is how you pass that var into dynamic statement
EXECUTE sp_executesql N'SELECT * FROM @MyTable', 
    N'@MyTable DefaultTable READONLY', 
    @MyTable