sql - 仅仅因为我想在代码中包含变量而使用存储过程而不是视图是否合理？

Question

我正在编写一个 Select 语句，出于可读性和清晰性的原因，我在内部使用了变量/表变量——在这个特定的上下文中，它使编码更容易理解。

由于我使用的是变量，因此我无法将此选择存储为VIEW，并且我想知道将其设为 aStored 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,
    [StartDate] [datetime] 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, StartDate, 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,
        StartDate,
        DurationInMonths,
        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,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 2, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 3, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 4, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 5, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 6, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 7, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 8, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 9, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 10, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 11, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end,
        CASE WHEN EOMONTH(DATEFROMPARTS(@CurrYear, 12, 1)) BETWEEN StartDate AND DATEADD(MONTH,DurationInMonths ,StartDate) then Rev_Monthly else 0 end   

    FROM @Revenue

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

    FETCH NEXT FROM year_cursor INTO @CurrYear 
END 

CLOSE year_cursor  
DEALLOCATE year_cursor 

SELECT 
RIT.* 
FROM @RevenueInTime RIT

标签： sqlsql-server