Question

SQL存储过程临时表问题

ALTER PROCEDURE [dbo].[BalanceToXls] -- Add the parameters for the stored procedure here @StartDate datetime, @EndDate datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @diffdate int; declare @i int; set @i=0; set @diffdate =DATEDIFF(day,@StartDate,@EndDate) print(@diffdate); drop table ##tmp1 SELECT b.Name,a.c_date, a.[mct_Today_Payout] ,a.[mct_Today_Income] ,a.[mct_Today_Balance] into ##tmp1 FROM 表1 as a,cfg_Org_MSite as b where a.c_Userid=b.id and a.c_date=@StartDate drop table ##tmp1 while(@i<@diffdate) begin set @i=@i+1;print(@i); set @StartDate=@StartDate+@i; SELECT b.Name,a.c_date, a.[mct_Today_Payout] ,a.[mct_Today_Income] ,a.[mct_Today_Balance] into ##tmp2 FROM 表1 as a,cfg_Org_MSite as b where a.c_Userid=b.id and a.c_date=@StartDate select a.*,b.c_date, b.[mct_Today_Payout] ,b.[mct_Today_Income] ,b.[mct_Today_Balance] into ##tmp3 from ##tmp1 as a,##tmp2 as b where a.Name=b.Name drop table ##tmp1 drop table ##tmp2 select * into ##tmp4 from ##tmp3 drop table ##tmp3 end select * from ##tmp1 drop table ##tmp1 drop table ##tmp2 drop table ##tmp3 END SQL2005编译无法通过，在WHILE循环里面，我DROP了一个##TMP1,然后再往##TMP1里面写入数据，这里理论上是没问题的，可是老是提示##TMP1已经存在，所以想来请教高手了

Answer 1

1.强调你的存储过程不规范
--到处多能看到drop
table
##tmp1
，其实第①②drop
table
##tmp1
都可以删除
多余的代码
2.while循环里面更是不能让人不唾弃
一个优秀的程序员
写的代码就是能让其他的程序员看得懂
3.临时表#可以用##也可以用
那样更简洁呢
4.建议理清思路
在重新来修改这个
相信你能理解的更透切~