Can't select from a successfully created #temp table
DECLARE @tmp_tab VARCHAR(20)
SELECT @TMP_TAB = '#TMP_TAB_BANK' + CAST(USER_ID(USER) AS NVARCHAR)
+ CAST(@@SPID AS NVARCHAR)
EXEC('CREATE TABLE ' + @TMP_TAB + (ID INT NULL, NAME VARCHAR NULL)')
//Break point
EXEC('select * from ' + @TMP_TAB)
I'm working in SQL Server 2005. In the code above I decide what to name my temp table. Then I create the temp table. If I run just these codes I receive Comman开发者_运维知识库ds completed successfully
message.
However if I execute the last line of code to retrieve the data (well, I know it's empty) I get
invalid object name '#TMP_TAB_BANK157'
Why can't I fetch records from a just created table? If the temp table was not created then why don't I get any warning?
#TEMP
tables are only available from the context they are created in. If you create #temp1
in one spid or connection, you can't access it from any other scope, except for child scopes.
If you create the #TEMP
table in dynamic SQL, you need to select from it in the same scope.
Alternatives are:
##GLOBAL
temp tables, which have their own risks.- Real tables
精彩评论