存储过程中使用临时表

CREATE PROCEDURE [dbo].[usp_UserFriendsOrder]  
 @s varchar(MAX)  
as
--如果临时表存在则先删除
if exists(select * from dbo.sysobjects where object_id('tempdb.dbo.#temp') is not null)
 delete from #temp
else
  create table #temp(F_ID int,F_UserID char(36),F_FatherID int,F_Code varchar(18),F_UserName varchar(20),F_Phone varchar(11),F_CreateUser char(36),F_CreateDate datetime, F_State int,F_Uplevel varchar(8000),F_DownLevel varchar(8000),F_Level int)
  --执行存储过程usp_TUserRelationByFID 放入临时表
  insert into #temp exec usp_TUserRelationByFID @s
  --查询临时表
   -- select * from #temp 
BEGIN
 -- A.Currentcount 本月消费条数
 -- B.Lastcount    上月消费条数
declare  @sqlf_id varchar(MAX) 
set @sqlf_id='SELECT  ltrim(Convert(numeric(9,2),case when B.Lastcount=0 then 0 else A.Currentcount*100.0/B.Lastcount end))+''%'' As Percentage, A.Currentcount,B.Lastcount  FROM (SELECT      count(*) as Currentcount
FROM         #temp  INNER JOIN 
                      dbo.T_Order ON #temp.F_UserID = dbo.T_Order.F_CreateUser  where  dbo.T_Order.F_Remark!=''商家收银'' and  datediff(month,dbo.T_Order.f_createdate,getdate())=0)as A,(SELECT      count(*) as Lastcount
FROM         #temp  INNER JOIN 
                      dbo.T_Order ON #temp.F_UserID = dbo.T_Order.F_CreateUser  where  dbo.T_Order.F_Remark!=''商家收银'' and  datediff(month,dbo.T_Order.f_createdate,getdate())=1)as B'
exec (@sqlf_id)
end
存储过程中使用临时表

推荐阅读
