存储过程内建临时表和临时函数，合并一个由存储过程返回的表

第一次发文章到首页，如果内容不好，请多见谅

由于项目需要，要在一个存储过程里建临时表和临时函数，来合并由一个存储过程综合了N个表的返回表，中间遇到了很多麻烦，现在终于弄好了。

先看最后的代码吧，一些注释在存储过程里有了：

 

CREATE PROCEDURE [dbo].[sp_ChangeStop_Second_Combination]
 @StartStopName nvarchar(64),
 @EndStopName nvarchar(64),
 @TopNumber int
AS
BEGIN
 SET NOCOUNT ON;

 If object_id('TemptbChangeStop')  is not null
   Drop Table TemptbChangeStop
 --通过sp_ChangeStop_Second_ResultByStopName存储过程，建立临时表
 Create Table TemptbChangeStop
 (
  StartStopName nvarchar(64),
  FirstLineName nvarchar(64),
  FirstTotal int,
  InterStopName nvarchar(64),
  SecondLineName nvarchar(64),
  SecondTotal int,
  EndStopName nvarchar(64),
  TotalStation int
 )
 Insert into TemptbChangeStop
 --sp_ChangeStop_Second_ResultByStopName存储过程返回的表为未合并的表
 Exec('sp_ChangeStop_Second_ResultByStopName '+@StartStopName+','+@EndStopName+',80')
 
 Declare @FirstFunction nvarchar(1024)
 
 Set @FirstFunction = 'Create Function dbo.f_ChangeStop_Combination1(@StartStopName nvarchar(64),@InterStopName nvarchar(64),@EndStopName nvarchar(64),@TotalStation int)
 Returns nvarchar(256)
 As
 Begin
 Declare @re varchar(256)
 Set @Re = ''''
 Select  @Re=
 --去掉经过的重复线路，注意：'',''+SecondLineName+''(''可以更精确的去掉重复线路，减少再一次的数据分拆和合并
 Case When Charindex('',''+FirstLineName+''('',@Re)>0
  Then @Re
  Else @Re+'',''+FirstLineName+''(经过''+Cast(FirstTotal As varchar) + ''站)''
 End
 From TemptbChangeStop
 Where StartStopName=@StartStopName And
  InterStopName=@InterStopName And
  EndStopName=@EndStopName And
  TotalStation=@TotalStation
 Return(Stuff(@Re,1,1,''''))
 End'

 --通过临时表，建立临时函数，合并第一次乘车线路和经过的站点条数
 If object_id('f_ChangeStop_Combination1')  is not null
   Drop function f_ChangeStop_Combination1
 --临时函数用Exec sp_executesql来建立
 Exec sp_executesql @FirstFunction
-- Go

 Declare @SecondFunction nvarchar(1024)

    Set @SecondFunction = 'Create Function dbo.f_ChangeStop_Combination2(@StartStopName nvarchar(64),@InterStopName nvarchar(64),@EndStopName nvarchar(64),@TotalStation int)
 Returns nvarchar(256)
 As
 Begin
 Declare @re varchar(256)
 Set @Re = ''''
 Select @Re=
 --去掉经过的重复线路，注意：'',''+SecondLineName+''(''可以更精确的去掉重复线路，减少再一次的数据分拆和合并
 Case When Charindex('',''+SecondLineName+''('',@Re)>0
  Then @Re
  Else @Re+'',''+SecondLineName+''(经过''+Cast(SecondTotal As varchar) + ''站)''
 End
 From TemptbChangeStop
 Where StartStopName=@StartStopName And
  InterStopName=@InterStopName And
  EndStopName=@EndStopName And
  TotalStation=@TotalStation
 Return(Stuff(@Re,1,1,''''))
 End'

 --通过临时表，建立临时函数，合并第二次乘车线路和经过的站点条数
 If object_id('f_ChangeStop_Combination2')  is not null
   Drop function f_ChangeStop_Combination2
 --临时函数用Exec sp_executesql来建立
 Exec sp_executesql @SecondFunction
-- Go
 --得到最终数据
 Select Top(@TopNumber) StartStopName,FirstPassBy=dbo.f_ChangeStop_Combination1(StartStopName,InterStopName,EndStopName,TotalStation),InterStopName,SecondPassBy=dbo.f_ChangeStop_Combination2(StartStopName,InterStopName,EndStopName,TotalStation),EndStopName,TotalStation
 From TemptbChangeStop
 Group By StartStopName,InterStopName,EndStopName,TotalStation
 Order By TotalStation

 If object_id('f_ChangeStop_Combination1')  is not null
   Drop function f_ChangeStop_Combination1
 If object_id('f_ChangeStop_Combination2')  is not null
   Drop function f_ChangeStop_Combination2
-- Go，这里用Go会出错的
 If object_id('TemptbChangeStop')  is not null
   Drop Table TemptbChangeStop
END

执行未合并的存储过程的结果如下：

 Exec('sp_ChangeStop_Second_ResultByStopName '+'新街口'+','+'新安江街'+',50')

 

合并后结果如下：

Declare @Time datetime
Set @Time = getdate();
Exec('sp_ChangeStop_Second_Combination 新街口,新安江街,10' )
Select datediff(ms,@Time,getdate())

平均执行300多到400多毫秒，时间上比较多，以后我还要优化。

如果各位有其它的方法，可以优化查询效率，希望告诉我下。

这个系统是南京公交查询系统，提供手机短信查询和WEB结合的查询，现在核心结构和核心代码都写好了，处理细节更花时间 :)

www.nj84.com和短信输入南京公交到12114查询(现在WEB还没怎么做好，功能也没放上去，以后用它会象GOOGLE那样提示的)，感兴趣的欢迎和我交流。 :)