Home Forums SQL Server 2012 SQL Server 2012 - T-SQL CTE within a stored proc RE: CTE within a stored proc

  • August 28, 2015 at 9:36 am

    #1823088

    mar.ko (8/28/2015)

    Ed Wagner (8/28/2015)

    The statement that contains the CTE needs to end with a query that uses the CTE. Like Luis said, please post the code; that should clear up the confusion.

    Bingo - therein lies the problem. I was not aware of that restriction.

    Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.

    temp tables exist for the duration. that's the tool you want.

    CTE's are just a convenient way to format/visualize a sub select.

    you can't propagate a sub select twice without doing the same work twice.(two queries cannot use it separately)

    if it needs to exist, throw it in a temp table.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!