在不同站点上计算费率时，我们总是使用不同的位置名称等。因此，我想创建一个存储过程，这样当我在某些站点上运行存储过程时，便可以声明这些值，而不必一直在脚本中进行更改。我已经成功创建了过程并声明了搜索值，但是似乎无法将此“声明的值”传递给要创建的临时表的列名的别名。

DeclARE @TimePeriod as INT = '15'
Declare @LocID1 as varchar(max) = 'IN02'
Declare @LocID2 as varchar(max) = 'IN03'
Declare @LocID3 as varchar(max) = 'PL01'
Declare @GroupLocID1 as varchar(max) = 'IN%'
Declare @GroupLocID2 as varchar(max) = 'PL%'
Declare @GroupLocID3 as varchar(max) = 'IN9%'

--Create Procedure ThroughputTest 
--@TimePeriod INT,--@LocID1 varchar(max),--@LocID2 varchar(max),--@LocID3 varchar(max),--@GroupLocID1 varchar(max),--@GroupLocID2 varchar(max),--@GroupLocID3 varchar(max) 

--AS

SELECT temp.starttime,temp.endtime,cast(SUM(CASE WHEN lc.Loc1 = @LocID1 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 = @LocID1 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 = @LocID1 THEN 1.00 ELSE 0.00 END) /@TimePeriod as decimal(18,2))
        AS LocID1,cast(SUM(CASE WHEN lc.Loc1 = @LocID2 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 = @LocID2 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 = @LocID2 THEN 1.00 ELSE 0.00 END) /@TimePeriod as decimal(18,2))
        AS LocID2,cast(SUM(CASE WHEN lc.Loc1 = @LocID3 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 = @LocID3 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 = @LocID3 THEN 1.00 ELSE 0.00 END) /@TimePeriod as decimal(18,2))
        AS LocID3,cast(SUM(CASE WHEN lc.Loc1 LIKE @GroupLocID1 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 LIKE @GroupLocID1 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 LIKE @GroupLocID1 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
        AS GroupLocID1,cast(SUM(CASE WHEN lc.Loc1 LIKE @GroupLocID2 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 LIKE @GroupLocID2 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 LIKE @GroupLocID2 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
        AS GroupLocID2,cast(SUM(CASE WHEN lc.Loc1 LIKE @GroupLocID3 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc2 LIKE @GroupLocID3 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
    + cast(SUM(CASE WHEN lc.Loc3 LIKE @GroupLocID3 THEN 1.00 ELSE 0.00 END)/@TimePeriod as decimal(18,2))
        AS GroupLocID3
    FROM (
        SELECT CAST(FORMAT(time_orig,'yyyy-MM-dd HH:mm:00') AS DATETIME) AS starttime,CAST(FORMAT(DATEADD(MINUTE,@TimePeriod,time_orig),'yyyy-MM-dd HH:mm:00') AS DATETIME) AS endtime
        FROM dbo.ThroughPut
        WHERE time_orig  <= getdate()-(1/24/4)
        GROUP BY
            CAST(FORMAT(time_orig,'yyyy-MM-dd HH:mm:00') AS DATETIME),'yyyy-MM-dd HH:mm:00') AS DATETIME)
    ) temp
    INNER JOIN dbo.Throughput lc ON lc.Time_Orig BETWEEN temp.starttime AND temp.endtime
GROUP BY starttime,endtime
ORDER BY starttime,endtime

--Exec ThroughputTest 
--@TimePeriod = '15',--@LocID1 = 'IN02',--@LocID2 = 'IN03',--@LocID3 = 'PL01',--@GroupLocID1 = 'IN%',--@GroupLocID2 = 'PL%',--@GroupLocID3 = 'IN9%'

完成后，我将着眼于使代码循环，因为要计算的位置可以在1到X的任意位置，但是当我到达该位置时，它将找出该部分。