Call a Stored Procedure in SQL Cte

Call a Stored procedure in SQL CTE

No, sorry. SELECTs statments only

If you need to use stored proc output (result set), then it'd be a temp table

CREATE TABLE #foo (bar int...)

INSERT #foo (bar, ...)
EXEC myStoredProc @param1...

-- more code using #foo

How to use stored procedure inside CTE

I got Same Type of Error Yesterday....

My Problem is that with Error...

I solved my problem using print with SemiColon..

you can check this picture

So..basically...you have to do ...put this line above CTE function in stored procedure

PRINT 'This Semicolumn is mandatory here ' ;

Stored Procedure Syntax with CTE

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:

1. Create one or more "temp tables". These are your CTE expressions, and there can be more than one.
2. 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=...

Return Stored Proc Results in CTE

Answer adapted from dialogue in comments:

You can use a stored procedure to populate a table variable, which Read Only access does allow you to create. You won't need to use OpenRowSet to populate it either. Just do:

INSERT INTO @MyTableVariable
EXEC MyStoredProcedure

I do this in a lot of places myself where I need to treat Stored Proc results as a table that I can JOIN or UNION with other tables.

Execute multiple statements and CTE in stored procedure in SQL Server 2012?

You have something strange going on because the following works for me:

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM [Table_1] WHERE ( [lname]=@SOMEUSER );

    WITH CTE AS (
        SELECT [fname], [lname]
        FROM [Table_1]
        WHERE ( [ID] = @TYPE )
    )
    INSERT INTO [Table_1]
    SELECT top 1
        @SOMEUSER,
        [lname],
        @TYPE
    FROM CTE 
END

Check your environment

A CTE is just syntax - it does not help performance

issue on with CTE in stored procedure

WITH needs to be followed by SELECT (or UPDATE or DELETE or INSERT) but not SET, and at least one of the common table expressions defined should be referenced by the following statement. You do not follow WITH by SELECT (or UPDATE or DELETE or INSERT).

Additionally the scope of the dynamic sql will not recognize a common table expression (cte) formed by the procedure prior to execution of the query string.

Specifies a temporary named result set, known as a common table
expression (CTE). This is derived from a simple query and defined
within the execution scope of a single SELECT, INSERT, UPDATE, or
DELETE statement. This clause can also be used in a CREATE VIEW
statement as part of its defining SELECT statement.

see: WITH common_table_expression

Call stored procedure into recursion cte

You can just insert your ordered results into a #temp table and use a cursor to iterate over the results something like this:

;WITH CTE 
AS
(
    SELECT PNLId ,concat('/',cast(PNLId as nvarchar(MAX)) )as tree, PNLParentId
    FROM [dbo].[DimPNL]
    WHERE PNLParentId IS NULL 
    UNION ALL
    SELECT T1.PNLId,concat( CTE.tree ,'/',cast(t1.PNLId as nvarchar(MAX))), T1.PNLParentId
    FROM [dbo].[DimPNL] AS T1
    INNER JOIN CTE
    ON T1.PNLParentId = CTE.PNLId
)
-- order the results into a #temp table
SELECT *
INTO #temp
FROM CTE
ORDER BY PNLId, PNLParentId DESC -- set this accordingly

DECLARE @pnlId int   
DECLARE @pnlParentId int   

DECLARE db_cursor CURSOR FOR  
SELECT PNLId, PNLParentId 
FROM #temp 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @pnlId, @pnlParentId    

WHILE @@FETCH_STATUS = 0   
BEGIN   
       -- call ytour stored proc with required params here
       Exec [spGetResultstarting] @pnlId, @pnlParentId

       FETCH NEXT FROM db_cursor INTO @pnlId, @pnlParentId   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

---

Related Topics