我正在编写Select语句，出于可读性和清晰性原因，我在内部使用了变量/表变量-在这种特定情况下，它使代码更易于理解。

由于我使用变量，因此无法将此选择存储为VIEW，我想知道是否将其设为Stored Procedure是正确的方法。

我知道我可以使用表值函数等构建不同的视图，但是对我来说至关重要的是，整个“解决方案”都包含在一个SQL对象中（环境之间的易于部署，可跟踪性）。>

那么Stored Procedure是正确的路吗？还是不应该使用它代替视图？

代码：

-- Supporting tables

DeclARE @Years TABLE (Year INT)
DeclARE @RevenueInTime TABLE (
    [RequestID] [nvarchar](20) NULL,[StartDate] [datetime] NULL,[Duration] [bigint] NULL,[OneTime] [bigint] NULL,[Monthly] [bigint] NULL,[year] [bigint] NULL,[m1] [bigint] NULL,[m2] [bigint] NULL,[m3] [bigint] NULL,[m4] [bigint] NULL,[m5] [bigint] NULL,[m6] [bigint] NULL,[m7] [bigint] NULL,[m8] [bigint] NULL,[m9] [bigint] NULL,[m10] [bigint] NULL,[m11] [bigint] NULL,[m12] [bigint] NULL
)
DeclARE @Revenue TABLE(
    [RequestID] [varchar](255) NULL,[DurationInmonths] [bigint] NULL,[Rev_OneTime] [bigint] NULL,[Rev_Monthly] [int] NULL
)

DeclARE @CurrYear INT 
DeclARE @MinYear INT
DeclARE @MaxYear INT
;

/* 
Create Table holding esseintal information about each Request (record per Request) 
ID,StartDate,Duration,One-Time Revenue,Monthly Revenue
*/
WITH OneTime AS (
SELECT
    RequestId,ROUND(SUM(Revenue),0)  AS Rev_OneTime
FROM costs
WHERE NonRecurring = 'Y'
GROUP BY RequestId
),Reccuring_one AS (
SELECT
    RequestId,SUM(Revenue) AS Rev_Reccuring,MAX(DurationInmonths) AS Duration
FROM costs
WHERE NonRecurring = 'N'
GROUP BY RequestId
),Recurring AS (
SELECT
    RequestId,CASE 
        WHEN Duration = 0 THEN 0 
        ELSE ROUND(Rev_Reccuring / Duration,0 ) 
    END AS Rev_Monthly
FROM Reccuring_one
),DistRequests AS (
SELECT
    DISTINCT RequestID,DurationInmonths

FROM costs
),Revenue AS (
SELECT 
    DR.RequestID,DR.StartDate,DR.DurationInmonths,IsnULL(CAST(OT.Rev_OneTime AS bigint),0) AS Rev_OneTime,IsnULL(CAST(R.Rev_Monthly AS INT),0 ) AS Rev_Monthly

FROM DistRequests DR
LEFT JOIN OneTime OT ON DR.RequestId = OT.RequestId 
LEFT JOIN Recurring R ON DR.RequestId = R.RequestId
)

INSERT INTO @Revenue SELECT * FROM Revenue



-- Calculate Timeframe that should be in scope,i.e year of minimum start date - untill year of maxiumm end date

SET @MinYear = (SELECT MIN(YEAR(StartDate)) FROM @Revenue)
SET @MaxYear = (SELECT MAX(YEAR(DATEADD(MONTH,DurationInmonths,StartDate))) FROM @Revenue)

SET @CurrYear = @MinYear

-- Table populated with each consecutive year for examined period
WHILE @CurrYear <= @MaxYear
BEGIN
    INSERT INTO @Years VALUES (@CurrYear)
    SET @CurrYear = @CurrYear + 1
END 

SET @CurrYear = @MinYear
;



-- Loop through each year,and create record for each requests that is ongoing during it
DeclARE year_cursor CURSOR FOR
SELECT  Year FROM @Years

OPEN year_cursor
FETCH NEXT FROM  year_cursor INTO @CurrYear

WHILE @@FETCH_STATUS = 0  
BEGIN  
    INSERT INTO @RevenueInTime
    (
        [RequestID],[StartDate],[Duration],[OneTime],[Monthly],[year],[m1],[m2],[m3],[m4],[m5],[m6],[m7],[m8],[m9],[m10],[m11],[m12]
    )


    SELECT 
        RequestID,Rev_OneTime,Rev_Monthly,@CurrYear AS year,--If inspected month is between StartDate and End Date of project set RevenueMonthly,else set 0
        --Use EOMONTH to make sure that all Requests regardless of DAY of StartDate qualify
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear,1,1)) BETWEEN StartDate AND DATEADD(MONTH,StartDate) then Rev_Monthly else 0 end,CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear,2,3,4,5,6,7,8,9,10,11,12,StartDate) then Rev_Monthly else 0 end   

    FROM @Revenue

    WHERE YEAR(StartDate) <= @CurrYear  AND    YEAR(DATEADD(MONTH,StartDate))   >= @CurrYear

    FETCH NEXT FROM year_cursor INTO @CurrYear 
END 

CLOSE year_cursor  
DEALLOCATE year_cursor 

SELECT 
RIT.* 
FROM @RevenueInTime RIT