Parsing of a stored procedure, issues with WHERE that has a nested function

[viaConBodhi]

I was parsing a stored procedure and found a particular issue involving a WHERE which has a nested WHERE and was then returning several other statements after this statement within the WHERE statement. After tracking the sqlparse.parse(sql) logic back to _change_splitlevel in statement_splitter I discovered the CREATE at the beginning of the stored procedure was causing the issue. After I sliced out all text prior to BEGIN and used only the text from BEGIN forward then the parser worked as expected. There were several "UPDATE" statements that were returned with the WHERE statement prior to me removing the CREATE text to BEGIN.

WHERE X NOT IN (SELECT Y FROM Z WHERE M IS NOT NULL); -- COMMENT UPDATE LMNO SET V = H,

[andialbrecht]

Thanks for reporting! This seems to be related to #692

Could you provide a (preferably minimal) example of a complete procedure statement and the changes you've made to _change_splitlevel?

[viaConBodhi]

The entire procedure started with the following

\r\n\r\nCREATE PROCEDURE [dbo].[PopulateDimension] \r\nAS\r\nBEGIN\r\n\t-

The statement that was returned included tokens where the final token in that statement was a WHERE token. That WHERE token captured several downstream UPDATE procedures but also left a few other downstream UPDATE procedures out of that token. I troubleshooted by feeding in only the several section of sql that were bundled as one token and sql.parse worked as expected. I then walked back through the sql.parse function and realized the lexer was parsing as expected. I then continued to walk the process back to the _change_splitlevel where I noticed the comments regarding CREATE. Since my use case only requires starting from begin, I simply parsed out all text prior to BEGIN and sql.parse worked as expected. I realize this may not be helpful for updating the code base but I wanted to create a record so someone else who may be searching for a solution to why the parsing in bundling several functions and a possible method to work around. BTW...thanks for this package.

    INSERT INTO Cartoon_Dimension(CARTOON_ID,
                                CARTOON_CODE,
                                CARTOON_NAME,
                                CARTOON_TITLE,
                                CARTOON_STATUS,
                                CARTOON_NUMBER,
                                CARTOON_DEPARTMENT,
                                CARTOON_SOURCE_SYSTEM,
                                CARTOON_CREATED_BY,
                                CARTOON_CREATED_DATE,
                                CARTOON_LAST_UPDATED_BY,
                                CARTOON_LAST_UPDATED_DATE)
    SELECT
    -1,--cast(PrOid as int) AS CARTOON_ID,
    CartoonNum AS CARTOON_CODE,
    CartoonNum AS CARTOON_NAME,
    CartoonTitle AS CARTOON_TITLE,
    'Not Available' AS CARTOON_STATUS,
    CartoonNum AS CARTOON_NUMBER,
    'Not Available' AS CARTOON_DEPARTMENT,
    'Cartoon A' AS CARTOON_SOURCE_SYSTEM,
    CARTOON_CREATED_BY,
    GETDATE() AS CARTOON_CREATED_DATE,
    CARTOON_CREATED_BY AS CARTOON_LAST_UPDated_BY,
    GETDATE() AS CARTOON_LAST_UPDATED_DATE
    FROM
    [Cartoon_STAGING].dbo.STG_Catoon_Form
    WHERE CartoonNum NOT IN (SELECT CARTOON_NUMBER FROM Cartoon_Dimension WHERE CARTOON_NUMBER IS NOT NULL);

    -- COMMENT
    UPDATE  Cartoon_Dimension
    SET     CARTOON_RECORD = CARTOON.Record,
            CARTOON_SUBMISSION_TYPE = CARTOON.ReviewCategory,
            CARTOON_APPROVAL_EXPIRATION_DATE = CARTOON.DateExpiration,
            CARTOON_APPROVAL_STATUS = CARTOON.Approved,
            CARTOON_DESC = CARTOON.DescH,
            CARTOON_GOALS = CARTOON.DescA,
            CARTOON_DESIGN = CARTOON.Design
    FROM    Cartoon_Dimension, Cartoon_STAGING.dbo.STG_Cartoon_Form CARTOON
    WHERE   Cartoon_Dimension.CARTOON_NUMBER = CARTOON.NUM;