存储过程临时表的问题

[hxy2436]

以下是我写的一个存储过程,为什么执行的时候总是提示"对象#tempTable无效",
急呀,请高手进

CREATE PROCEDURE [UP_Envelope_Select]
(
@general_Envelope_Check_FLag int =0, --the first-coming of Envelope
@back_Envelope_Check_FLag int =0, --the flag of back's Envelope
@PageSize int = 15, --the size of a page
@PageIndex int = 1 --the index of the page
)
AS
begin
DECLARE @SelectStatement NVARCHAR(500)
DECLARE @SelectFirstPage NVARCHAR(200)
DECLARE @SelectIndexPage NVARCHAR(500)
DECLARE @DropStatement NVARCHAR(50)

SET @SelectStatement = ' SELECT id,title,submitdate,isback into #tempTable FROM dbo.T_Envelope where CheckedFlag='+ str(@general_Envelope_Check_FLag) + ' and isback=0
union
select DISTINCT id,title,submitdate,isback from Envelope_Back where isback=1 and RefuseFlag=' + str(@back_Envelope_Check_FLag)

SET @SelectFirstPage = ' SELECT TOP ' + str(@PageSize) + ' * FROM #tempTable '
PRINT @SelectFirstPage
EXECUTE sp_executesql @SelectFirstPage
end
GO

[wuyq11]

create table #tempTable
()
SELECT TOP ' + str(@PageSize) + ' * FROM #tempTable
先建，再查询

[hxy2436]

SET @SelectStatement = ' SELECT id,title,submitdate,isback into #tempTable FROM dbo.T_Envelope where CheckedFlag='+ str(@general_Envelope_Check_FLag) + ' and isback=0
union
select DISTINCT id,title,submitdate,isback from Envelope_Back where isback=1 and RefuseFlag=' + str(@back_Envelope_Check_FLag)

SET @SelectFirstPage = ' SELECT TOP ' + str(@PageSize) + ' * FROM #tempTable '

我是先给里面写值再查询的呀,
要是分开在查询分析器里就对着呢,
但是合成到存储过程中就出错了

[tian_fang]

有没有temp表啊?

[zetee]

原因是。该对象没找到，你先执行
SELECT id,title,submitdate,isback into #tempTable FROM dbo.T_Envelope where 1!=1

再执行 @SelectStatement

[FireTiger]

#tempTable的作用域的问题吧？
#tempTable是存在于内存中的，当结束一次 对话时，系统会自动释放这个内表。
所以分布执行动态sql语句时，上下文对话不同肯定找不到#tempTable


set @SelectFirstPage = @SelectStatement +@SelectFirstPage
EXECUTE sp_executesql @SelectFirstPage
这样试下吧。

[jiangshun]

我想，把临时表建在SET @SelectStatement 这个前面

[ljhcy99]

CREATE PROCEDURE [UP_Envelope_Select]
(
@general_Envelope_Check_FLag int =0, --the first-coming of Envelope
@back_Envelope_Check_FLag int =0, --the flag of back's Envelope
@PageSize int = 15, --the size of a page
@PageIndex int = 1 --the index of the page
)
AS
begin
DECLARE @SelectStatement NVARCHAR(500)
DECLARE @SelectFirstPage NVARCHAR(200)
DECLARE @SelectIndexPage NVARCHAR(500)
DECLARE @DropStatement NVARCHAR(50)

SET @SelectStatement = ' SELECT id,title,submitdate,isback into #tempTable FROM dbo.T_Envelope where CheckedFlag='+ str(@general_Envelope_Check_FLag) + ' and isback=0
union
select DISTINCT id,title,submitdate,isback from Envelope_Back where isback=1 and RefuseFlag=' + str(@back_Envelope_Check_FLag)

EXECUTE （@SelectStatement ）; tmp表还没有做成，还没有记录，怎么能查询
SET @SelectFirstPage = ' SELECT TOP ' + str(@PageSize) + ' * FROM #tempTable '
PRINT @SelectFirstPage
EXECUTE sp_executesql @SelectFirstPage
end
GO

[mengxj85]

SELECT id,title,submitdate,isback  into #tempTable  FROM dbo.T_Envelope where CheckedFlag=str(@general_Envelope_Check_FLag) and isback=0 
                  union 
                  select DISTINCT id,title,submitdate,isback  from Envelope_Back where isback=1 and RefuseFlag=str(@back_Envelope_Check_FLag) 

SET @SelectFirstPage = ' SELECT TOP ' + str(@PageSize) + ' * FROM #tempTable '  

[greatverve]

--================临时表入门示例================== 
--================张树强==081218================ 
create procedure [dbo].[Test_TempTable] 
as 
begin 
    declare @indextable table(id int identity(1,1),nid int) --这个临时表，有一个id主键，自增。 
    insert into @indextable (nid) select id from ttest --把数据插入临时表 
    select * from @indextable 
end 
GO 

[greatverve]

http://blog.csdn.net/greatverve/archive/2008/12/01/3421186.aspx
临时表的简单应用。

[greatverve]

哦，我的不是#temptable，但是我的也是临时表。