DECLARE CURSOR fails layout

Verifex

I've found that inside a sproc I have with a declared Cursor using a WITH statement causes the SQL layout to report a failure, when none is actually there.

I ran validate on the SQL in SSMS and it works fine, but SQL prompt doesn't recognize it has valid SQL, unfortunately this also seems to affect SQL Compare, as this same sproc that the layout engine doesn't like is being treated as though it doesn't exist as a sproc when I do a comparison from the script directory on my hard drive against the database.

Here's a sample of the code that SQL Prompt doesn't like:

DECLARE c_WinGroupMarkings CURSOR LOCAL FORWARD_ONLY STATIC FOR
WITH OrderedRoleList(RoleID, ARoleName, MarkName, ALevel) AS 
(
     SELECT roleid, rolename, markname, 0 from @roletable
     WHERE parent is null and rolename != 'public'
     UNION ALL
     SELECT a.roleid, a.rolename, a.markname, a.level FROM @roletable a
     INNER JOIN GlobalRoles b
     ON b.category = a.category
)

Any help?

Chris Auckland

Thanks for your post.

I wanted to contact you via your email address to try and gather some more information, but unfortunately I couldn't reach you.

When I try to parse the SQL in SSMS I get a syntax error, presumably because the code extract is not valid on its own. I have never created a cursor using a WITH statement before and I have not found any documentation that supports this kind of declaration. As a result of this I have has little success in recreating a test case to confirm if this is a bug or not.

If the SQL Code parses in SSMS but SQL Prompt identifies an error, then it sounds like it could be a bug with SQL Prompt. However, I will need to be able to recreate the issue in order to report the bug to the developers.

It would be really useful if you could supply me with code that demonstrates the problem.

I look forward to hearing from you.