What should I do if the temporary table created in dynamic SQL cannot be accessed?

kittast

SELECT @ N = MAX (LEN (F0080)) FROM KLDB.DBO.OTH_D_NEWS

--Temporary table, the maximum ID is the maximum length of the relevant stock field
SET @ SQL = 'SELECT TOP' + CONVERT (VARCHAR (10), @ N) + 'ID = IDENTITY (INT, 1,1)
INTO # FROM SYSCOLUMNS A, SYSCOLUMNS B '

EXECUTE SP_EXECUTESQL @SQL

SELECT * FROM #
---------------------------------------
Server: Message 208, Level 16, State 1, Process P_GENERATE_TB8002, Line 20
Object name '#' is invalid.

What should I do?

prajna

Do not build temporary tables, manually drop after use

恶魔右指

@SQL
Defined as nvarchar

fanfan1980

Try with global temporary tables

terryyang

#tableName ----> ## tableName

我是杀人狂

SELECT @ N = MAX (LEN (F0080)) FROM KLDB.DBO.OTH_D_NEWS

--Temporary table, the maximum ID is the maximum length of the relevant stock field
SET @ SQL = 'SELECT TOP' + CONVERT (VARCHAR (10), @ N) + 'ID = IDENTITY (INT, 1,1)
INTO #a FROM SYSCOLUMNS A, SYSCOLUMNS B '

EXECUTE SP_EXECUTESQL @SQL

SELECT * FROM #a


Temporary table also needs table name--#

fanfan1980

--try

SELECT @ N = MAX (LEN (F0080)) FROM KLDB.DBO.OTH_D_NEWS

--Temporary table, the maximum ID is the maximum length of the relevant stock field
SET @ SQL = 'SELECT TOP' + CONVERT (VARCHAR (10), @ N) + 'ID = IDENTITY (INT, 1,1)
INTO ## T FROM SYSCOLUMNS A, SYSCOLUMNS B '

EXECUTE SP_EXECUTESQL @SQL

SELECT * FROM ## T

恶魔右指

The landlord's usage is incorrect, take a look at the online help is more useful to you