CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionwith-statement
user2644964
user2644964

Reputation: 803

SQL with clause in stored procedure

Is it possible to define a with clause in a stored procedure and use it in if else statements because I always get an error?

BEGIN
    WITH Test (F, A) AS
    (
        SELECT FM.ID, FM.Name 
        FROM [Test.Abc] FM
        INNER JOIN [Organization] O on O.ABCID = FM.ID
    )

    IF(@var = 'case1')
    BEGIN
        SELECT * 
        FROM Test F
        WHERE NOT F.ID = 'someID'
    END

I always get an "Incorrect syntax" error before the if statement

If I move the with clause into the if statement it works fine. But I need the with statement outside to reuse it in different if else statements.

Upvotes: 0

Views: 21743

Answers (3)

SqlZim
SqlZim

Reputation: 38023

Here's another version of the same answers you're getting:

Your with common table expresson has to be in the same statement as the query that calls it, and it has to be referenced by a query (or other cte) or it is a syntax error.

Reference the documentation Guidelines for Creating and Using Common Table Expressions.

BEGIN -- doing stuff
-- .... doing stuff over here
IF(@var = 'case1')
    BEGIN
        with Test (F, A) as ( -- You define columns ID as F and Name as A
        select FM.ID, FM.Name from [Test.Abc] FM
        inner join [Organization] O on O.ABCID = FM.ID
          )     
        select * from Test 
        where not F = 'someID' -- You must use the new columna name (F)
    END
-- .... and doing some other stuff over here too
END -- done with this stuff

Upvotes: 3

David דודו Markovitz
David דודו Markovitz

Reputation: 44911

WITH is not a standalone, it always a part of a whole statement and only one statement.
It is not recognizable outside the scope ofits statement.

BEGIN

    with my_cte (n) as (select 1+1)
    select * from my_cte

    -- The following statement yields the error "Invalid object name 'my_cte'."    
    -- select * from my_cte

END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use a temporary table or table variable. The scoping rules of SQL Server ensure that such a table is dropped at the end of the procedure:

BEGIN
    select FM.ID, FM.Name
    into #test
    from [Test.Abc] FM inner join
         [Organization] O
         on O.ABCID = FM.ID;

    IF(@var = 'case1')
        BEGIN
            select *
            from #Test F
            where not F.ID = 'someID'
        END;

This has the advantage that you can add indexes to the table, and these might improve performance.

Upvotes: 2

Related Questions