Where can I put a SQL script that creates temp tables and will allow joins?

Question

I've got a script that ideally would live in a view but because it creates a temp table SQL Server won't allow it.

I need to be able to call it and make joins from various stored procedures so it'd be nice to have it contained in a single spot if changes need to be made instead of embedding it all over the place. I don't think a stored procedure will work because I don't believe you can join on SP result sets.

What would work?

Here's the script:

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


DECLARE divCursor CURSOR FOR
SELECT [FiscalPeriod]
      ,[SharePrice]
      ,[Rate]
  FROM [dbo].[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

a stored proc can call other stored procs . And within your final proc you can insert data into temp table(s) and do whatever join logic needs to be done. — junketsu, Dec 03 '18 at 20:35
Try rewriting without a cursor for starters. And if you have to use a cursor (I can't see why you do in this example), always declare them FAST_FORWARD. Also, don't use multiplication & division with floats or the 'money' type. if it's meant to represent currency. Use Numeric/Decimal types instead. — pmbAustin, Dec 03 '18 at 20:43
@pmbAustin I initially tried with no cursor using `LAG` but ended up with circular references that couldn't be resolved. `@divPrice` (`money`) and `@divFactor` (`float`) are matched to the type in the table column. Should I just cast from `money` and `float` to `decimal`? — Legion, Dec 03 '18 at 20:50
@junketsu I don't believe a SP can see temp tables created in another SP. At least not in my experience. Also, how would you join on an `EXEC` statement? — Legion, Dec 03 '18 at 20:53
You shouldn't be using "money" as a column type. It's more a display mask type to use for casting. Never use it if you're doing to do anything more than addition and subtraction. There are numerous internet articles on this. I'm still not understanding why you need a cursor (or LAG)… you're just selecting from a table, doing simple calculations, and inserting into a TEMP table. Mind explaining why you can't just do that all with set operations in one go? — pmbAustin, Dec 03 '18 at 21:00
@pmbAustin The OpeningUnits of the current row is the value of EndingUnits calculated in the previous row. But EndingUnits is dependent on the value of OpeningUnits in the current row. Basically I ended up with infinitely nested LAG statements. — Legion, Dec 03 '18 at 21:09
Can you share some data from the table and sample output needed. — MD AZAD HUSSAIN, Dec 03 '18 at 21:09
@Legion refer to this https://stackoverflow.com/questions/33727313/temp-table-in-stored-procedure-is-unavailable-after-first-go ... just keep in mind that 2nd stored proc calling 1st Sp (which has temp tables) will error out you have to replace them with table variables. I have ran into this issue for accounting dept. That one column that no one knows logic of but Want the value. — junketsu, Dec 04 '18 at 13:10

score 1 · Accepted Answer · answered Dec 03 '18 at 21:36

This can be accomplished inside a view as a CTE. Below is a sample of your code with a table variable replacing dbo.Pricing which shows the output of your SQL and the 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;

But it's still using a temp table which is not allowed in a view. — Legion, Dec 03 '18 at 21:52
@Legion look again. The cte is standalone. I provided your version so you could compare the results — UnhandledExcepSean, Dec 03 '18 at 21:56
@Legion I wasn't sure about the data types for the decimals, so I just made it pretty big. Did you get a chance to try this? — UnhandledExcepSean, Dec 04 '18 at 14:10

score 0 · Answer 2 · answered Dec 03 '18 at 20:59

You can't join a stored procedure, but you can "join" a scalar function via outer apply. https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

You can't create a temp table within a user-defined function, but you can use table variables. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-2017

Beside this, I think you could solve your scenario without any of this. Will came back later

Where can I put a SQL script that creates temp tables and will allow joins?

2 Answers2