请帮忙看一下下面的存储过程为什么不正确
ALTER PROCEDURE [dbo].[StatisticTelNumsByUsers]
-- Add the parameters for the stored procedure here
@tbName varchar(50), --订单信息表
@beginTime datetime, --开始时间
@endTime datetime --统计结束时间
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
delete from tab_UserStatisticsTels
declare @strSql varchar(200)
set @strSql= ' '
set @strSql= 'select userId,count(*) as telNums into #temp1 from '+@tbName+
' where OrderDateTime> = '+
' ' ' '+dbo.GetLongStrDateTime(@beginTime,0)+ ' ' ' '+ ' and OrderDateTime <= '+
' ' ' '+dbo.GetLongStrDateTime(@endTime,0)+ ' ' ' '+ ' group by all userid ';
print(@strSql);
--删除员工电召统计表内容
exec(@strSql)
set @strSql= ' '
set @strSql= 'select userId,count(*) as HaveCarsNums into #temp2 from '+@tbName+
' where OrderDateTime> = '+
' ' ' '+dbo.GetLongStrDateTime(@beginTime,0)+ ' ' ' '+ ' and OrderDateTime <= '+
' ' ' '+dbo.GetLongStrDateTime(@endTime,0)+ ' ' ' '+ ' and state=1 group by all userid ';
print(@strSql)
exec(@strSql)
select * from #temp1
select * from #temp2
select t1.userId,t1.telNums,t2.HaveCarsNums into #temp from #temp1 t1,#temp2 t2
where t1.userId=t2.userId
insert into tab_UserStatisticsTels(userid,telNums,HaveCarsNums) select * from #temp
drop table #temp1
drop table #temp2
drop table #temp
END
提示 temp1，temp2对象不存在
[解决办法]
不需要用临时表:
INSERT INTO tab_UserStatisticsTels(userid,telNums,HaveCarsNums)
SELECT userId, COUNT(*) AS telNums,
SUM(CASE state WHEN 1 THEN 1 END) AS HaveCarsNums
FROM @tbName
WHERE OrderDateTime BETWEEN @beginTime AND @endTime
GROUP BY userId
