在哪里可以放置创建临时表并允许连接的SQL脚本?

这可以在作为CTE的视图内完成。下面是用表变量替换dbo.Pricing的代码示例,其中显示了SQL和CTE的输出。

DECLARE @Pricing TABLE (
    [FiscalPeriod] smalldatetime,
    [SharePrice] DECIMAL(24,13),
    [Rate] DECIMAL(24,13)
)

INSERT INTO @Pricing ([FiscalPeriod],[SharePrice],[Rate]) SELECT '2018-01-01',10.25,0.01
INSERT INTO @Pricing ([FiscalPeriod],[SharePrice],[Rate]) SELECT '2018-04-01',10.50,0.01
INSERT INTO @Pricing ([FiscalPeriod],[SharePrice],[Rate]) SELECT '2018-07-01',10.86,0.01


DECLARE @fiscalPeriod smalldatetime,
        @openingUnits float,
        @divPrice money,
        @divFactor float,
        @drip float,
        @endingUnits float


DECLARE divCursor CURSOR FOR
SELECT [FiscalPeriod]
      ,[SharePrice]
      ,[Rate]
  FROM @Pricing
  ORDER BY FiscalPeriod
OPEN divCursor
FETCH NEXT FROM divCursor
INTO @fiscalPeriod, @divPrice, @divFactor

    SET @openingUnits = 1
    SET @drip = @openingUnits/@divPrice*@divFactor
    SET @endingUnits = @openingUnits + @drip

    SELECT
            FiscalPeriod = @fiscalPeriod, OpeningUnits = @openingUnits, DivPrice = @divPrice
        ,   DivFactor = @divFactor, DRIP = @drip, EndingUnits = @openingUnits + @drip
    INTO #Temp

    FETCH NEXT FROM divCursor
    INTO @fiscalPeriod, @divPrice, @divFactor

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @openingUnits = @endingUnits
        SET @drip = @openingUnits/@divPrice*@divFactor
        SET @endingUnits = @openingUnits + @drip

        INSERT INTO #Temp (FiscalPeriod, OpeningUnits, DivPrice, DivFactor, DRIP, EndingUnits)
        VALUES (@fiscalPeriod, @openingUnits, @divPrice, @divFactor, @drip, @endingUnits)

        FETCH NEXT FROM divCursor
        INTO @fiscalPeriod, @divPrice, @divFactor
    END

CLOSE divCursor
DEALLOCATE divCursor

SELECT * FROM #Temp

DROP TABLE #Temp;

--CTE VERSION
WITH fp_cte ([FiscalPeriod],[DivPrice],[DivFactor],[Row])
AS (
    SELECT
            [FiscalPeriod]
        ,   [SharePrice] AS [DivPrice]
        ,   [Rate] AS [DivFactor]
        ,   ROW_NUMBER() OVER (ORDER BY [FiscalPeriod] ASC) AS [Row]
    FROM @Pricing
)
,calc_cte ([FiscalPeriod],[OpeningUnits],[DivPrice],[DivFactor],[DRIP],[EndingUnits],[Row]) 
AS  
(  
    SELECT
            [FiscalPeriod]
        ,   CONVERT(DECIMAL(24,13),1) AS [OpeningUnits]
        ,   [DivPrice]
        ,   [DivFactor]
        ,   CONVERT(DECIMAL(24,13),1.0/[DivPrice]*[DivFactor]) AS [DRIP]
        ,   CONVERT(DECIMAL(24,13),1+(1.0/[DivPrice]*[DivFactor])) AS [EndingUnits]
        ,   [Row]
    FROM fp_cte
    WHERE [Row]=1
    UNION ALL
    SELECT
            p2.[FiscalPeriod]
        ,   CONVERT(DECIMAL(24,13),p1.[EndingUnits]) AS [OpeningUnits]
        ,   p2.[DivPrice]
        ,   p2.[DivFactor]
        ,   CONVERT(DECIMAL(24,13),p1.[EndingUnits]/p2.[DivPrice]*p2.[DivFactor]) AS [DRIP]
        ,   CONVERT(DECIMAL(24,13),p1.[EndingUnits]+(p1.[EndingUnits]/p2.[DivPrice]*p2.[DivFactor])) AS [EndingUnits]
        ,   p2.[Row]
    FROM calc_cte p1
    INNER JOIN fp_cte p2 ON p1.[Row]=(p2.[Row]-1)
)  
SELECT [FiscalPeriod],[OpeningUnits],[DivPrice],[DivFactor],[DRIP],[EndingUnits]
FROM calc_cte;

不能联接存储过程,但可以通过外部应用“联接”标量函数。 https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-2017