Stored Procedure Syntax with CTE

Question

This is probably trivial but I am just learning about CTE (thanks to help here).

I have a procedure that is used to determine totals.

The first part is the totals are the sum of a position at their level an below. So I needed a way to retrieve records that (1) determined the level of the record (hierarchy) and (2) returned all records at and below. That was asked and answered here.

Now want to take the CTE table from the answer above and use it in the second part of my procedure (get the totals)

CREATE PROCEDURE [dbo].[GetProgramTotals]
    @programId nvarchar(10) = null,
    @owner int = null,
    @totalAmount money OUT,
    @usedAmount money OUT,
    @remainingAmount money OUT
AS
BEGIN
    WITH rCTE AS
    (
        SELECT 
            *, 0 AS Level 
        FROM Forecasting.dbo.Addressbook 
        WHERE Addressbook = @owner

        UNION ALL

        SELECT
            t.*, r.Level + 1 AS Level
        FROM Addressbook t
        INNER JOIN rCTE r ON t.ParentAddressbook = r.Addressbook
    )

    Select @totalAmount = (Select Sum(Amount) from dbo.Budget where 
                                (@programId IS NULL or (ProgramId = @programId)) and (@owner IS NULL or (BudgetOwner in (SELECT Addressbook from rCTE))))


    Select @usedAmount = (Select Sum(SubTotal) from dbo.OrderLine where 
                                (@programId IS NULL or (ProgramId = @programId) and (@owner IS NULL) or (Budget in (SELECT Addressbook from rCTE))))

    if (@totalAmount is null)
        set @totalAmount = 0

    if (@usedAmount is null)
        set @usedAmount = 0

    Set @remainingAmount = (@totalAmount - @usedAmount)
END

The idea of this procedure is the dynamically calculate an individual (or all) programs based an a users position in a hierarchy.

So a regional managers totals would be the sum of all districts and district reps.

UPDATE: I updated this based on squillman (thank you) comment below.

Now I have a different problem. When I execute the proc - I get 'Invalid object name rCTE'.

Does this answer your question? [Use one CTE many times](https://stackoverflow.com/questions/10196808/use-one-cte-many-times) — Michael Freidgeim, Oct 25 '21 at 01:05

squillman · Accepted Answer · 2016-04-22T19:11:28.000

You can't use SET in the middle of a query like that. Change it to a SELECT and it should remedy your syntax error.

CREATE PROCEDURE [dbo].[GetProgramTotals]
    @programId nvarchar(10) = null,
    @owner int = null,
    @totalAmount money OUT,
    @usedAmount money OUT,
    @remainingAmount money OUT
AS
BEGIN
    WITH rCTE AS(
    SELECT *, 0 AS Level FROM Forecasting.dbo.Addressbook WHERE Addressbook = @owner
    UNION ALL
    SELECT t.*, r.Level + 1 AS Level
    FROM Addressbook t
    INNER JOIN rCTE r ON t.ParentAddressbook = r.Addressbook)

    SELECT @totalAmount = (Select Sum(Amount) from dbo.Budget where 
                                (@programId IS NULL or (ProgramId = @programId)) and (@owner IS NULL or (BudgetOwner in (SELECT Addressbook from rCTE))))



    , @usedAmount = (Select Sum(SubTotal) from dbo.OrderLine where 
                                (@programId IS NULL or (ProgramId = @programId) and (@owner IS NULL) or (Budget in (SELECT Addressbook from rCTE))))

    if (@totalAmount is null)
        set @totalAmount = 0

    if (@usedAmount is null)
        set @usedAmount = 0

    Set @remainingAmount = (@totalAmount - @usedAmount)

END

CTE's can be a bit confusing at first, but they are really quite simple once they make sense. For me it clicked when I began thinking of them as just another temp table syntax (pro-tip: they're not in reality, just conceptually). So basically:

Create one or more "temp tables". These are your CTE expressions, and there can be more than one.
Perform a standard operation using one or more of the CTE expressions in the statement immediately following your CTE(s).

As Martin mentioned in comments below, the CTE(s) are only scoped for the next immediate statement and fall out of scope after that.

So,

;WITH cte1 AS
       (
        SELECT Col1 FROM Table1
       ),
      cte2 AS
       (
        SELECT Col1 FROM Table2
       )
SELECT Col1 FROM cte1 //In scope here
UNION
SELECT Col1 FROM cte1; //Still in scope since we're still in the first statement

SELECT Col1 FROM cte1; //Fails.  cte1 is now out of scope (as is cte2)

In your case you're using the recursive CTE to form a parent/child hierarchy and then setting variables based on the results. Your CTE syntax is pretty close after the edit, you just need the comma to bring things back together into one statement.

//Variable assignment example
;WITH cte1 AS
       (
        SELECT Col1 FROM Table1
       ),
      cte2 AS
       (
        SELECT Col1 FROM Table2
       )
SELECT @var1 = (SELECT TOP 1 Col1 FROM cte1)
      ,@var2 = (SELECT TOP 1 Col1 FROM cte2) //You're missing the comma at the start of this line

Change Select @usedAmount=... to , @usedAmount=...

Probably worth mentioning explicitly that the CTE is only in scope for the statement immediately following as the OP may not be aware. — Martin Smith, Apr 22 '16 at 18:55
@MartinSmith Yeah, there are a few things to improve. I'm dissecting it a bit and am going to expand on my answer. — squillman, Apr 22 '16 at 18:56
@squillman I just updated it thank you. A new problem came up and it is more than likely what MartinSmith is speaking to. Since I need to use this rCTE table twice - any ideas on how to reuse the data for the second Select in? — JDBennett, Apr 22 '16 at 19:00
@John-DavidBennett comma delimit the two different variable assignments inside the same select as this answer does. — Martin Smith, Apr 22 '16 at 19:04

Stored Procedure Syntax with CTE

1 Answers1