A built-in temporary table and temporary function are used to merge a table returned by the stored procedure.

If the content is not good, please forgive me

To meet project requirements, we need to create temporary tables and temporary functions in a stored procedure to merge the return tables of N tables in a stored procedure. This creates a lot of trouble, now we have finally finished it.

Let's take a look at the final code. Some annotations are included in the stored procedure:

 

Create procedure [DBO]. [sp_changestop_second_combination]
@ Startstopname nvarchar (64 ),
@ Endstopname nvarchar (64 ),
@ Topnumber int
As
Begin
Set nocount on;

If object_id ('tempchangestop') is not null
Drop table temptbchangestop
-- Use sp_changestop_second_resultbystopname to create a temporary table
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: tables returned by the stored procedure are unmerged tables.
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 =
-- Remove the repeat line. Note: '','' + secondlinename + ''('' can more accurately remove the repeat line and reduce the re-data splitting and merging.
Case when charindex ('','' + firstlinename + ''('', @ Re)> 0
Then @ Re
Else @ Re + '','' + firstlinename + ''(after'' + Cast (firsttotal as varchar) + ''site )''
End
From temptbchangestop
Where startstopname = @ startstopname and
Interstopname = @ interstopname and
Endstopname = @ endstopname and
Totalstation = @ totalstation
Return (stuff (@ Re, 1,1 ,''''))
End'

-- Create a temporary function through a temporary table to merge the number of first bus routes and the number of stations that pass through
If object_id ('f _ changestop_combination1 ') is not null
Drop function f_changestop_combination1
-- Temporary functions are created using 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 =
-- Remove the repeat line. Note: '','' + secondlinename + ''('' can more accurately remove the repeat line and reduce the re-data splitting and merging.
Case when charindex ('','' + secondlinename + ''('', @ Re)> 0
Then @ Re
Else @ Re + '','' + secondlinename + ''(after'' + Cast (secondtotal as varchar) + ''site )''
End
From temptbchangestop
Where startstopname = @ startstopname and
Interstopname = @ interstopname and
Endstopname = @ endstopname and
Totalstation = @ totalstation
Return (stuff (@ Re, 1,1 ,''''))
End'

-- Create a temporary function through a temporary table, and merge the second route and number of stations that pass
If object_id ('f _ changestop_combination2 ') is not null
Drop function f_changestop_combination2
-- Temporary functions are created using exec sp_executesql
Exec sp_executesql @ secondfunction
-- Go
-- Get final data
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: errors may occur when you use go.
If object_id ('tempchangestop') is not null
Drop table temptbchangestop
End

The result of executing a stored procedure that is not merged is as follows:

Exec ('SP _ changestop_second_resultbystopname '+ 'xinjiekou' + ',' + 'xinanjiang Street '+', 50 ')

 

The merged result is as follows:

Declare @ time datetime
Set @ time = getdate ();
Exec ('SP _ changestop_second_combination Xinjiekou, Xinanjiang Street, 10 ')
Select datediff (MS, @ time, getdate ())

The average execution speed is from 300 to 400 milliseconds, and the time is relatively large. I will optimize it later.

If you have other methods, you can optimize the query efficiency.

This system is a Nanjing Public Transit Query System that provides text message query and web-based query. Now the core structure and core code are all well written, so it takes more time to process details.

Enter www.nj84.com and text message to Nanjing Public Transit to 12114 for query (now the Web is not ready yet, and the function is not available yet, and it will prompt like Google in the future ), if you are interested, please contact me.