请指点一个存储过程的问题??

[WangZWang]

在执行下面的语句时为何出现提示'对象#YBReport无效',对象#YBReport
我已经建立了, 还有没有动态建立表的方法, 谢谢!

Create procedure up_xxx
@IfDetail int
As
Set NoCount on
Declare @CreateStr nvarchar(4000)

If @IfDetail=0
Begin

Set @CreateStr=' Create Table #YBReport(
DeptCode varchar(20),
Finance Char(2),
SumMoney Money,
tzrc Int,
tzFare Money,
tzAvgFare Money
)
CREATE INDEX [IX_#YBReport] ON [dbo].[#YBReport]([DeptCode]) ON [PRIMARY]'
End
Else
Begin
Set @CreateStr='Create Table #YBReport(
DeptCode varchar(20),
Sickid Int,
Visitid Int,
SickCode varchar(10),
SickName varchar(16),
Finance Char(2),
SumMoney Money,
tzFare Money,
tzAvgFare Money
)
CREATE INDEX [IX_#YBReport] ON [dbo].[#YBReport]([DeptCode],[Sickid],[VisitID]) ON [PRIMARY]'
End

Exec(@CreateStr)

Select * from #YBReport

truncate table #YBReport
drop table #YBReport

go

Exec up_xxx 0

[WangZWang]

在执行下面的语句时为何出现提示'对象#YBReport无效',对象#YBReport
我已经建立了, 还有没有动态建立表的方法, 谢谢!

Create procedure up_xxx
@IfDetail int
As
Set NoCount on
Declare @CreateStr nvarchar(4000)

If @IfDetail=0
Begin

Set @CreateStr=' Create Table #YBReport(
DeptCode varchar(20),
Finance Char(2),
SumMoney Money,
tzrc Int,
tzFare Money,
tzAvgFare Money
)
CREATE INDEX [IX_#YBReport] ON [dbo].[#YBReport]([DeptCode]) ON [PRIMARY]'
End
Else
Begin
Set @CreateStr='Create Table #YBReport(
DeptCode varchar(20),
Sickid Int,
Visitid Int,
SickCode varchar(10),
SickName varchar(16),
Finance Char(2),
SumMoney Money,
tzFare Money,
tzAvgFare Money
)
CREATE INDEX [IX_#YBReport] ON [dbo].[#YBReport]([DeptCode],[Sickid],[VisitID]) ON [PRIMARY]'
End

Exec(@CreateStr)

Select * from #YBReport

truncate table #YBReport
drop table #YBReport

go

Exec up_xxx 0

[zheninchangjiang]

将最后的那三句也写进 @createstr,#t 只在exec()过程中有效

[liang922]

应该这样
set @CreateStr=@CreateStr +
' select * from #ybreport
truncate table #YBReport
drop table #YBReport
'

exec (@CreateStr)

[wtadminxjeri]

Exec(@CreateStr) --执行完这步，实际上#YBReport已经消失了

Select * from #YBReport

[47522341]

#ybreport在執行語句Exec(@CreateStr)後就被釋放掉了;
局部臨時變量不能這麼用的;
解決方式就是按照頂樓的兄弟的意思;
將Select * from #YBReport

truncate table #YBReport
drop table #YBReport
也加入動態執行語句中就可以了;

[WangZWang]

在中间有一段语句,我要先对临时表#ybreport进行操作??

[zheninchangjiang]

那也没有办法，在EXEC外就是无效