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;